Saturday, September 17, 2011

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])
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
----- ------
-- 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.

No comments:

Post a Comment