SQLcommitted

SQL Server 2012–Logical Functions

Choose() and IIF() are two new logical functions introduced in SQL Server 2012.

Choose() :

It returns the item at the specified index from a list of values.

Syntax:

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

Lets find some facts about Choose() function.

Fact 1: Item index starts from 1

Ex1:

Select Choose(3,‘V’,‘A‘,‘C’,‘B’) As Result –- Return C as output as C is present at 3rd place

Result
——
C

Fact 2: It returns NULL if index value exceeds the bounds of the array

Ex2:

Select Choose(-2, ‘A’,2,6,7,8.8) As Result –- Index value –2 is exceeds the bound of the array it returns NULL

Result
—————————————
NULL

Fact 3 – It returns the data type with the highest precedence from the set of types passed to the function

Ex3:

Select Choose(3,10,12,13,15,17.6) As Result /* in the item list fractional number has higher precedence than integer */

Result
—————————————
13.0

Fact 4: If the provided index value has a numeric data type other than int, then the value is implicitly converted to an integer

Ex:

Select Choose(3.7, 1,2,5,8) As Result –- index value 3.7 converted to int

Result
———–
5

IIF():

it returns one of two values, based on the Boolean expression evaluates to true or false.

Syntax:

IIF ( boolean_expression, true_value, false_value )

It returns the data type with the highest precedence from the types in true_value and false_value

Ex:

/* The below Boolean expression is true so it will return 45 and as from the two values  45 and 40.0 40.0 has higher precedence so it will return 45.0*/

Declare @a int = 45;
Declare @b float = 30;
Select IIF ( @a > @b, 45,40.0 ) As Result

Result
—————————————
45.0

April 27, 2012 - Posted by SQLcommitted | SQL Info