New Logical Functions in SQL Server 2012

Logical Functions

IIF( ) Function:
            The IFF function is used for check a condition. Suppose A>B, In this condition ‘A’ is the first expression and ‘B’ is the second expression. If the first expression evaluates to TRUE then the first value is displayed, if not the second value is displayed.

Syntax:

IIF (booleanexpression, true_value, false_value)

Example: Depends on student marks, it displays result as pass or fail.

Declare @M int
Set @M=45
Select iif(@M>35,’Pass’,’Fail’)

In this example student marks is @M=45; If the condition is true it returns ‘Pass’ or Result is false it returns ‘Fail’.

Output:

Choose() Function
This function returns a value out of a list based on its index number. You can think of it as an array king of thing. The index number here starts from 1.

Syntax:

Choose(index, value1, value2, value3, value4 ......... valueN)


The above function contains two parameters,
• Index: index is an integer expression that represents an index into the list of the items. The list index always stats at 1.
• Value: List of values of any data type.

Example 1: If an index inside the bound of the array

Declare @indexnumber int
Set @indexnumber=4
Select choose(@indexnumber,55,42,35,25,26,35,88,45,22)

In the preceding example we use index=4. It will start at 1. Choose() returns 25 as output since 25 is present at @index location 4.

Output:

Example 2: If an index exceeds the bound of the array it returns NULL

Declare @indexnumber int
Set @indexnumber=10
Select choose(@indexnumber,55,42,35,25,26,35,88,45,22)

Output:

In this example we use index=10. It will start at 1.Choose() returns Null as output because in the item list the index value exceeds the bounds of the array; the last index=9