Logical Functions in SQL SERVER 2012 || CHOOSE and IIF functions in SQL Server 2012
This video will provide you the details of each Logical Function in SQL Server 2012.
*****************************************************************************
Logical Functions (Transact-SQL)
CHOOSE (Transact-SQL)
Returns the item at the specified index from a list of values in SQL Server.
Syntax
CHOOSE ( index, val_1, val_2 [, val_n ] )
Arguments
index
Is an integer expression that represents a 1-based index into the list of the items following it.
If the provided index value has a numeric data type other than int, then the value is implicitly converted to an integer. If the index value exceeds the bounds of the array of values, then CHOOSE returns null.
val_1 … val_n
List of comma separated values of any data type.
Return Types
Returns the data type with the highest precedence from the set of types passed to the function.
Remarks
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.
DECLARE @a int = 3
select CASE WHEN @a=1
THEN 'DEVELOPER'
WHEN @a=2
THEN 'TESTER'
WHEN @a=3
THEN 'SUPPORT'
ELSE 'MANAGER' END AS RESULT
*****************************************************************************
IIF (Transact-SQL)
Returns one of two values, depending on whether the Boolean expression evaluates to true or false in SQL Server.
Syntax
IIF ( boolean_expression, true_value, false_value )
Arguments
boolean_expression
A valid Boolean expression.
If this argument is not a Boolean expression, then a syntax error is raised.
true_value
Value to return if boolean_expression evaluates to true.
false_value
Value to return if boolean_expression evaluates to false.
Return Types
Returns the data type with the highest precedence from the types in true_value and false_value.
Remarks
IIF is a shorthand way for writing a CASE expression. 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. The same rules that apply to the CASE expression for Boolean expressions, null handling, and return types also apply to IIF
The fact that IIF is translated into CASE also has an impact on other aspects of the behavior of this function. Since CASE expressions can be nested only up to the level of 10, IIF statements can also be nested only up to the maximum level of 10. Also, IIF is remoted to other servers as a semantically equivalent CASE expression, with all the behaviors of a remoted CASE expression.