Home > Denali, TSQL > SQL Server Denali – IIF() and CHOOSE() functions

SQL Server Denali – IIF() and CHOOSE() functions

SQL Server Denali introduced 2 new logical functions: IIF and CHOOSE.

IIF function

IIF function returns one of two values, depending on whether Boolean expression evaluates to true or false.
Syntax:  IIF (boolean_expression, true_value, false_value)
IIF function is simple replacement for:
Case when <boolean_expression>=true  then <true_value> else <false_value> end

Let’s see how IIF function works.

-- Simple IIF
DECLARE @a int = 45;
DECLARE @b int = 40;
SELECT IIF ( @a > @b, 'TRUE', 'FALSE' ) AS Result;

In next select statement, I will use IIF function to retrieve PriceDescription value. If ListPrice is greater the 1000 then set Expensive for PriceDscription else it is Cheap.

If we look at the execution plan, we will see that Compute Scalar is used to support IIF function. On Compute Scalar property we can see that IIF function is converted to CASE expression. So, the same rules that apply to the CASE statement also apply to IIF function.

IIF function can be nested but only up to the level of 10. Next examples show IIF nesting to 10th level and error if we try to nest IIF up to the level of 11.

CHOOSE function

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

Syntax: CHOOSE (index, val_1, val_2 [, val_n ])

Let’s see how CHOOSE function works.

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

In next query, I will show how CHOOSE function can be used in clauses such as select list, GROUP BY and ORDER BY.

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.

IIF and CHOOSE functions are nice additions to the language and it’s good to know how they work.

Categories: Denali, TSQL
  1. No comments yet.
  1. No trackbacks yet.

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s

%d bloggers like this: