Introduction
SQL Server 2012 introduces two new logical functions: CHOOSE and IIF. The CHOOSE logical function returns the item at a specified index from a list of values whereas IIF returns one of two values depending on whetehr the Boolean expression (that is the first argument) evaluates to true or false.
CHOOSE
The CHOOSE function returns the item at the specified index from the list of values. The CHOOSE function acts just like an index of an array; here the array is supplied as parameters. The index parameter determines which value needs to be returned.
Syntax
CHOOSE (index, value 1, value 2 [, ... , value n])
Argument / parameter
- Index: An integer expression. If index is a non-numeric data type then SQL Server implicitly converts it into an INT and if the conversion fails then this function raises an error. The index value starts from 1. If the index value is less than 1 or exceeds the length of the array then the CHOOSE function returns NULL.
- Value 1, value2, …, value n: These are a list of comma separated values of any data type.
Examples
-- CHOOSE example with Index as CHAR data type
SELECT CHOOSE ('1' , 'A', 'B', 'C', 'D' ) AS output;
--Output
-- A
-- CHOOSE example with Index as INT data type
SELECT CHOOSE (2 , 'A', 'B', 'C', 'D' ) AS output;
--Output
-- B
-- CHOOSE example with Index as FLOAT data type
SELECT CHOOSE ( 1.4, 'A', 'B', 'C', 'D' ) AS output;
--Output
-- A
SELECT CHOOSE ( 2.8, 'A', 'B', 'C', 'D' ) AS output;
--Output
-- B
-- CHOOSE example with non-exist Index
SELECT CHOOSE (0 , 'A', 'B', 'C', 'D' ) AS output;
--Output
-- NULL
SELECT CHOOSE (6 , 'A', 'B', 'C', 'D' ) AS output;
--Output
-- NULL
SELECT CHOOSE ('one' , 'A', 'B', 'C', 'D' ) AS output;
--Output
--Msg 245, Level 16, State 1, Line 1
--Conversion failed when converting the varchar value 'test' to data type int.
IIF
The IIF logical function returns one of two values depending on the first argument depedning on whether the Boolean expression values to true or false. The IIF function is also available in other languages, like VB, SQL Server Reporting Services and so on.
Syntax
IIF (Boolean Expression, True Value, False Value)
Argument / parameter
- Boolean Expression: This is a valid Boolean expression. If this argument is not a Boolean expression then an error is raised.
- True Value: It is the value to return if the Boolean Expression evaluates to true.
- False Value: It is the value to return if the Boolean Expression evaluates to false.
Examples
DECLARE @a int = 25, @b int = 20;
SELECT IIF ((@a - @b) > 0, 'TRUE', 'FALSE') as output
--Output
-- TRUE
DECLARE @c int = 20, @d int = 25;
SELECT IIF ((@c - @d) > 0, 'TRUE', 'FALSE') as output
--Output
-- FALSE
--IIF example with NULL
DECLARE @str VARCHAR(10);
SELECT IIF ( @str IS NULL, 'String Value is Null', 'String has value') as output
--Output
-- String Value is Null
--Nested IIF example
DECLARE @point int=2002
SELECT IIF( @point >10000, 'Platinum Member',
IIF( @point BETWEEN 2001- AND 10000, 'Gold Member',
IIF( @point BETWEEN 501 AND 2000, 'Silver Member ',
IIF( @point BETWEEN 101 AND 500, 'Bronze Member',
IIF( @point BETWEEN 0 AND 100, 'Starter Member', 'Member point not found' ))))) AS Grade
--Output
-- Gold Member
The Logical function IIF is a short way to write a CASE WHEN expression. Based on the first argument, the evaluation of this function returns either of other two arguments. Internally IIF is used CASE WHEN expression.
Conclusion
Both the logical functions CHOOSE and IIF internally use CASE WHEN. The performance of these functions (CHOOSE, IIF and CASE) is the same. The CHOOSE and IIF logical functions are the shortest way to use a CASE expression. A CASE expression can be nested up to 10 levels; in the same way an IIF expression can also be nested up to 10 levels.
We can select a CHOOSE and IIF logical function over a CASE expression for the following reasons.
-
Good Readability of SQL Query
-
Very easy to maintain
-
Required less line of code
-
IIF is very useful and compact when we are working with two possible outputs.