TSQL 2012 CHOOSE Function

Download PDF

In SQL Server 2012 there is a new function called CHOOSE that takes in an offset, and a list of options.  Choose is a function that returns the item at a specific index.

Syntax:
—CHOOSE(index, val_1, val_2, val_3, …)
—If the index is greater than the number of values or less than 1 it returns NULL
—Easier than using a CASE statement in some examples.
Example:
</div>
<div>
-- CHOOSE
-- returns the item at a specific index
declare @corners as int = 6
SELECT choose(@corners, 'point', 'line', 'triangle', 'square',
 'pentagon', 'hexagon', 'heptagon', 'octagon')

-- the old way using case.
SELECT CASE @corners
 WHEN 1 THEN 'point'
 WHEN 2 THEN 'line'
 WHEN 3 THEN 'triangle'
 WHEN 4 THEN 'square'
 WHEN 5 THEN 'pentagon'
 WHEN 6 THEN 'hexagon'
 WHEN 7 THEN 'heptagon'
 WHEN 8 THEN 'octagon'
 else NULL
 END;

In the above example, the CASE statement and CHOOSE function do the same thing, but the CHOOSE function is a bit easier to write.Here is another example

-- CHOOSE day of week example
DECLARE @day as int=4
SELECT CHOOSE(@day,'Sunday','Monday', 'Tuesday',
              'Wednesday','Thursday','Friday','Saturday')
For choose and other new TSQL functionality in Server 2012 take a look at my What’s New in TSQL 2012 presentation, which will be presented at Seattle Code Camp on June 16th.
 

More from Stedman Solutions:

SteveStedman5
Steve and the team at Stedman Solutions are here for all your SQL Server needs.
Contact us today for your free 30 minute consultation..
We are ready to help!

Leave a Reply

Your email address will not be published. Required fields are marked *

*