TSQL 2012 CHOOSE Function
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:
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