June 8, 2012 by SteveStedman
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')