SQL Server Denali : Choose

CHOOSE function returns a value from the given array based on the specified index position.

Syntax : Choose ([Position], [Value1],[Value2],…,[ValueN])

Here,
Position : The position number of the value to return. Position number starts from 1
[Value1],[Value2],…,[ValueN] : List of values.

Example :

SELECT CHOOSE(1, 'a', 'b', 'c') AS First,

CHOOSE(2, 'a', 'b', 'c') AS Second;

/*
First Second
----- ------
a     b
*/
-- If the index value exceeds the bounds of the array of values,
-- then CHOOSE returns null.

SELECT CHOOSE(0, 'a', 'b', 'c') AS First,

CHOOSE(4, 'a', 'b', 'c') AS Second

/*
First Second
----- ------
NULL  NULL
*/
-- If the provided index value has a numeric data type other than int,
-- then the value is implicitly converted to an integer

SELECT CHOOSE(2.5, 'a', 'b', 'c') AS First,

CHOOSE(3.9, 'a', 'b', 'c') AS Second;

/*
First Second
----- ------
b     c
*/


Example 2:
/* 
We can use Choose function in Select List, Group By Clause and Order By Clause also. 
*/

SELECT p.Name, SUM(s.OrderQty) SellQuantity, CHOOSE(DATEPART(QUARTER, s.ModifiedDate), '1st', '2nd', '3rd', '4th') AS Quarter

FROM Sales.SalesOrderDetail s INNER JOIN Production.Product p ON p.ProductID = s.ProductID

WHERE s.ModifiedDate >= '2006-01-01' AND s.ModifiedDate < ' 2007-01-01'

GROUP BY p.Name,CHOOSE(DATEPART(QUARTER, s.ModifiedDate), '1st', '2nd', '3rd', '4th')

HAVING SUM(s.OrderQty) > 600

ORDER BY p.Name, CHOOSE(DATEPART(QUARTER, s.ModifiedDate), '1st', '2nd', '3rd', '4th')

If we look at the execution plan, we will see that Compute Scalar is used to support CHOOSE function. The engine has taken the CHOOSE function and converted it into a CASE.