Microsoft® SQL Server® 2012 Bible

184

Part II: Building Databases and Working with Data

SET @q = 25;

SELECT CASE

WHEN @b = 2007 AND @q BETWEEN 10 AND 30 THEN 1

ELSE NULL

END AS Test;

Result:

Test

---------

1

New SQL Server 2012 Logical Functions

SQL Server 2012 introduces two new logical functions, IIF and CHOOSE.

The IIF Function

The IIF function is a shorthand version for the CASE statement. The syntax for the IIF

function is as follows:

IIF(boolean_expression, true_value, false_value)

The function behaves exactly as it does in Microsoft Excel. If the boolean expression

evaluates to true, the fi rst value is return, and if it evaluates to false, the second value is

returned. The following script illustrates the use of the function:

SELECT IIF(1=1, 'True', 'False') Condition

Result

Condition

-------------

True

The Choose Function

The CHOOSE function is also a logical operator, but it behaves in a somewhat different man-

ner. CHOOSE is similar to an index in an array, assuming the array is a list of arguments.

The syntax for the CHOOSE function is as follows:

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

Index is a 1-based integer that acts as the index into the list of values. The corresponding

values are the list of values that will be searched. The following script illustrates the use of

the function:

SELECT CHOOSE(3, 'Lions', 'Tigers', 'Bears') Chosen

Result:

Chosen

-----------

Bears

c08.indd 184c08.indd 184 7/30/2012 4:21:13 PM7/30/2012 4:21:13 PM

http://www.it-ebooks.info