# Category Archives: Logical Functions

## CHOOSE LOGICAL FUNCTION IN SQL SERVER 2012

CHOOSE is one of the new built-in logical function introduced as a Part of Sql Server 2012. It returns the value at the specified index position from the list of values.

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

Example 1: CHOOSE Basic Example

```SELECT CHOOSE(1,'Spring','Summer','Autumn','Winter')
SELECT CHOOSE(2,'Spring','Summer','Autumn','Winter')
SELECT CHOOSE(3,'Spring','Summer','Autumn','Winter')
SELECT CHOOSE(4,'Spring','Summer','Autumn','Winter')```

Result:

Example 2: CHOOSE function with variables as index and values.

```DECLARE @SeasonId INT = 2, @Season1 Varchar(10) = 'Spring',
@Season2 Varchar(10) = 'Summer',@Season3 Varchar(10) = 'Autumn',
@Season4 Varchar(10) = 'Winter'

SELECT CHOOSE(@SeasonId, @Season1, @Season2, @Season3, @Season4)```

Result: Summer

Example 3: CHOOSE will return NULL if INDEX position is outside the range of values.

```SELECT CHOOSE(-10,'Spring','Summer','Autumn','Winter')
SELECT CHOOSE(0,'Spring','Summer','Autumn','Winter')
SELECT CHOOSE(5,'Spring','Summer','Autumn','Winter')```

Result:

Example 4: If index value is numeric, it will be implicitly converted to INT.

```SELECT CHOOSE(3.1,'Spring','Summer','Autumn','Winter')
SELECT CHOOSE(3.85,'Spring','Summer','Autumn','Winter')```

Result:

Example 5: CHOOSE function Return Datatype will be the one with highest precedence from the Datatypes of the list of values

`SELECT CHOOSE(3, 40.58, 50, 60, 70)`

Result: 60.00

Example 6: CHOOSE function with Index Type as Varchar

`SELECT CHOOSE('TEST','Spring','Summer','Autumn','Winter')`

Result:
Msg 245, Level 16, State 1, Line 1
Conversion failed when converting the varchar value ‘TEST’ to data type int.

`SELECT CHOOSE('3','Spring','Summer','Autumn','Winter')`

Result: Autumn

You may like to read the below new built-in function’s introduced in Sql Server 2012:

 New Built in Functions introduced in Sql Server CONVERSION FUNCTIONS PARSE TRY_PARSE TRY_CONVERT STRING FUNCTIONS CONCAT FORMAT LOGICAL FUNCTIONS CHOOSE IIF DATE AND TIME FUNCTIONS EOMONTH DATEFROMPARTS DATETIMEFROMPARTS SMALLDATETIMEFROMPARTS DATETIME2FROMPARTS TIMEFROMPARTS DATETIMEOFFSETFROMPARTS

Please correct me, if my understanding is wrong. Comments are always welcome.

## IIF() LOGICAL FUNCTION IN SQL SERVER 2012

IIF() is one of the new built-in logical function introduced as a Part of Sql Server 2012. IIF() is the shorthand way of writing CASE statement or IF-ELSE statement. In-fact if we see the execution plan the IIF() function internally translates to a CASE statement.

IIF() function takes three arguments, first argument should be a Boolean expression otherwise it raises an exception. If Boolean expression evaluates to TRUE then Second argument will be the result otherwise Third argument will be the result. This functions result’s Datatype will be the one with highest precedence from the Datatypes of Second and Third Argument.

Syntax: IIF ( boolean_expression, true_value, false_value )

Now let us understand this function with examples:

Example 1: Comparing two Integer numbers

`SELECT IIF(1 > 10, 'TRUE', 'FALSE' )`

Result: FALSE

Example 2: Comparing two Integer Variables

```DECLARE @Marks INT = 60, @Minimum INT = 35
SELECT IIF(@Marks >= @Minimum, 'PASS', 'FAIL' )```

Result: PASS

Example 3: Comparing Strings

```DECLARE @NAME Varchar(50) = 'Basav'
SELECT IIF(@NAME IN ('Basav','Kalpana'),
'Likes SQL', 'Likes ORACLE' )```

Result: Likes SQL

Example 4: Result’s Datatype will be the one with highest precedence from the Datatypes of Second and Third Argument.

`SELECT IIF(1 > 10, 1.5, 40)`

Result: 40.0

Example 5: Nested IIF() Statement.

```DECLARE @Percentage AS NUMERIC(5,2) = 71
SELECT IIF(@Percentage >= 70, 'Distinction',
IIF(@Percentage>=35 AND @Percentage<70, 'Pass', 'Fail'))```

Result: Distinction

Note: As IIF() statement internally translates to an CASE statement, so the Max Nesting level is 10.

Example 6: IIF() with both return value arguments value as NULL constant

`SELECT IIF(1 > 2, NULL, NULL)`

Result:
Msg 8133, Level 16, State 1, Line 1
At least one of the result expressions in a CASE specification must be an expression other than the NULL constant

Example 7: IIF() with on of the return value argument value as NULL constant

`SELECT IIF(1 > 2, NULL, 'NO')`

Result: NO

Example 8: IIF() with both return value arguments are variables with value NULL

```DECLARE @A INT = NULL, @B INT = NULL
SELECT IIF(1 > 2, @A, @B)```

Result: NULL

You may also like to read other LOGICAL FUNCTION CHOOSE() introduced in SQL SERVER 2012

Please correct me, if my understanding is wrong. Comments are always welcome.

You may like to read the below new built-in function’s introduced in Sql Server 2012:

 New Built in Functions introduced in Sql Server CONVERSION FUNCTIONS PARSE TRY_PARSE TRY_CONVERT STRING FUNCTIONS CONCAT FORMAT LOGICAL FUNCTIONS CHOOSE IIF DATE AND TIME FUNCTIONS EOMONTH DATEFROMPARTS DATETIMEFROMPARTS SMALLDATETIMEFROMPARTS DATETIME2FROMPARTS TIMEFROMPARTS DATETIMEOFFSETFROMPARTS