SQL Server Denali – New Logical Functions

There are two new Logical functions are introduced in Denali. Let‘s have a look

• IFF
• CHOOSE

IFF

It Returns one of two values, depending on whether the Boolean expression evaluates to true or false.

IIF is a shorthand way for writing a CASE statement. It evaluates the Boolean expression passed as the first argument, and then returns either of the other two arguments based on the result of the evaluation. That is, the true_value is returned if the Boolean expression is true, and the false_value is returned if the Boolean expression is false or unknown. true_value and false_value can be of any type.

--Syntax 

IIF ( boolean_expression, true_value, false_value )

--Example 

SELECT IIF (50 > 20, True, False) AS Result;

--Result 

True

CHOOSE

Returns the item at the specified index from a list of values.

CHOOSE acts like an index into an array, where the array is composed of the arguments that follow the index argument. The index argument determines which of the following values will be returned.

--Syntax 

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

-- Example 

SELECT CHOOSE ( 3, 'Varinder', 'Dinesh', 'Ranjoyt', 'Vikram' ) AS Result;

--Result 

Ranjoyt