New Logical Functions in SQL Server 2012

Today, I have provided an article showing you how to utilize the new logical functions in SQL Server 2012. These are the two functions:

1. Choose()
2. IIF()

Let's take a look at a practical example. The example is developed in SQL Server 2012.

Choose() Function

This function is used to return the value out of a list based on its index number. You can think of it as an array kind of thing. The Index number here starts from 1.

Syntax

CHOOSE ( index, value1, value2.... [, valueN ] )

CHOOSE() Function excepts two parameters,

Index - Index is an integer expression that represents an index into the list of the items. The list index always starts at 1.

Value - List of values of any data type.

Now some facts related to the Choose Function

1. Item index starts from 1

DECLARE @Index INT;

SET @Index=5;

Select Choose(@Index,'R','O','H','A','T','A','S','H') As ChooseResult -- Return T as output as T is present at @Index=5 place

 

In the preceding example we take index=5. It will start at 1. Choose() Returns T as output since T is present at @Index location 5.

OUTPUT

2.  When passed a set of types to the function it returns the data type with the highest precedence; see:

DECLARE @Index INT;

SET @Index=5;

Select Choose(@Index,10,22,8.6,13,15,17.6)  As CooseResult

In this example we use index=5. It will start at 1. Choose() Returns 15.0 as output since 15 is present at @Index location 5 because in the item list, fractional numbers have higher precedence than integers.

3. If index value exceeds the bound of the array it returns NULL

DECLARE @Index INT;

SET @Index=9;

Select Choose(@Index,'R','O','H','A','T','A','S','H')  As CooseResult

In this example we take index=9. 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=8.

OUTPUT

4. If the index value is negative then that exceeds the bounds of the array therefore it returns NULL; see:

DECLARE @Index INT;

SET @Index=-1;

Select Choose(@Index,'R','O','H','A','T','A','S','H')  As CooseResult

In this example we take index= -1. It will start at 1. Choose() Returns Null as output because in the item list the index value exceeds the bounds of the array.

OUTPUT

5. If the provided index value has a float data type other than int, then the value is implicitly converted to an integer; see:

DECLARE @Index INT;

SET @Index=4.5;

Select Choose(@Index,25,28,9.6,13,15,20) As CooseResult

In this example we take index= 4.5. It will start at 1.  If the specified index value has a float data type other than int, then the value is implicitly converted to an integer. It returns the 13.0 as output since 15 is present at @Index=4.5 which means index is 4.

OUTPUT

IIF() Function

The IIF function is used to 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 the first value is displayed, if not the second value is displayed.

Syntax

IIF ( boolean_expression, true_value, false_value )

Example

DECLARE @a INT;

SET @a=50;

DECLARE @b INT;

SET @b=60;

Select iif(@a>@b, 50, 60) As IIFResult

In this example a=50 and b=60; that means the condition is false  Select iif(@a>@b, 50, 60) As IIFResult. It returns false value that is 60.

OUTPUT