Click here to Skip to main content
Click here to Skip to main content

SQL Server's built-in scalar Logical Functions (IIF() and CHOOSE())

, 28 Jun 2013
Rate this:
Please Sign up or sign in to vote.
SQL Server's Logical Functions (IIF() and CHOOSE()), New functions in SQL Server, Examples for IIF and CHOOSE, Comparison

Table Of Content  

Introduction    

SQL Server 2012 comes with two new scalar functions under the newly created logical functions category. A scalar logical function performs a logical operation or comparison on objects and expressions and returns a Scalar value.  

The two new scalar functions introduced  under the newly created logical function category are  

The CHOOSE logical function returns the item at the specified index from a list of values while the IIF logical function returns one of two values, depending on whether the Boolean expression evaluates to true or false. 

In this article I'm going to explain the above two functions with simple practical examples. 

Background  

SQL Server 2012 adds many new built in functions to the exiting sql function cateogories. The new functions that are added to the existing sql function categories are given below, 

  • String Functions
    • FORMAT() 
    • CONCAT()   
  • Conversion functions 
    • PARSE () 
    • TRY_PARSE ()
    • TRY_CONVERT ()
  • Date and time functions 
    • DATEFROMPARTS ()
    • DATETIMEFROMPARTS () 
    • DATETIME2FROMPARTS ()
    • SMALLDATETIMEFROMPARTS ()
    • DATETIMEOFFSETFROMPARTS ()
    • TIMEFROMPARTS ()
    • EOMONTH ()   

All the above functions are covered in my previous articles, You can read more from the below mentioned links.

In this article we will go through over IIF() and CHOOSE() logical functions.  

IIF()  

IIF function is already a known function in other languages and its been added new in SQL Server from version 2012. IIF Logical Function returns one of two values, depending on whether the Boolean expression evaluates to true or false. IIF is a shorthand way for writing a CASE expression and IIF is internally a CASE statement. 

IIF ( boolean_expression, true_value, false_value )  

Where boolean_expression is a valid Boolean expression, true_value is the value to return if boolean_expression evaluates to true and false_value is the value to return if boolean_expression evaluates to false.

Note: If the first parameter is a not valid Boolean expression, then a syntax error is raised. At least one of the result expressions in a IIF/CASE specification must be an expression other than the NULL constant otherwise an error will be raised.  

SELECT IIF(1,'1','0') AS 'IIF'
 
/*
Msg 4145, Level 15, State 1, Line 1
An expression of non-boolean type specified in a context where a condition is expected, near '('.
*/  
 
SELECT IIF ( 1>2, NULL, NULL ) AS 'IIF'
 
/*
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.
*/ 

IIF logical function is very useful and handy when working with two possible outcomes based on a condition. In actual IIF is translated into a CASE statement and IIF can be nested upto 10 levels same like CASE

IIF() Usage 

IIF function is very easy to understand, but still, I have given some simple practical examples below, 

Simple IIF() 

DECLARE @Salary INT
DECLARE @AvgSalary INT
SET @AvgSalary = 10000
SET @Salary = 20000
SELECT IIF(@Salary > @AvgSalary,'Salary is greater than Average Salary',
					'Salary is less than Average Salary') AS 'IIF -Salary'
 
/*  -Output-
IIF -Salary
------------------------------------
Salary is greater than Average Salary
*/  

In the above query IIF checks for salary greater than average salary, As the above conditon evaluates to true the true_value is getting returned from the IIF

Nested IIF() 

While we write program in any languages we often required to evaluate conditions in a nested way. The below transact-sql shows nested  IIF logical function usage.  For demonstrating a real time scenario I have used a table variable to store some dummy data and applied IIF logical function in the SELECT statement to the the Students Grades.  

DECLARE @StudentMarks TABLE (Name VARCHAR(100),Mark INT)
INSERT @StudentMarks VALUES('SNS_A',98)
INSERT @StudentMarks VALUES('SNS_B',64)
INSERT @StudentMarks VALUES('SNS_C',83)
INSERT @StudentMarks VALUES('SNS_D',75)
INSERT @StudentMarks VALUES('SNS_F',65)
 
SELECT Name,IIF( Mark BETWEEN 90 AND 100, 'A Grade',
            IIF( Mark BETWEEN 80 AND 89,  'B Grade',
            IIF( Mark BETWEEN 70 AND 79,  'C Grade',
            IIF( Mark BETWEEN 66 AND 69,  'D Grade',
            IIF( Mark < 65, 'Failed', 'E Grade' ))))) AS Grade
FROM @StudentMarks
 
/* -Output-
Name	Grade
--------------
SNS_A	A Grade
SNS_B	Failed
SNS_C	B Grade
SNS_D	C Grade
SNS_F	E Grade
*/ 

The above code is just for demonstrating Nested IIF, we can use a CASE for a much cleaner approach. 

IFF() with NULL  

NULL Expression 
DECLARE @NullVariable VARCHAR(1)
SELECT IIF(@NullVariable IS NULL,'VALUE IS NULL',
					'VALUE IS NOT NULL') AS 'IIF Sample'
 
/* -output-
IIF Sample
-------------
VALUE IS NULL
*/ 

In the above statement the Boolean expression checks for NULL and returns the value appropriately.  

NULL Constants  
SELECT IIF ( 1>2, NULL, NULL ) AS 'IIF'
 
/*
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.
*/
 
SELECT IIF ( 1>2, NULL, '1>2' ) AS 'IIF'
 
/* -Output-
IIF
---
1>2
*/ 

We cannot have both the return value as NULL constant. 

NULL Parameters 
DECLARE @n1 INT = NULL, @n2 INT = NULL;
SELECT IIF ( 1 > 2, @n1, @n2 ) AS 'IIF';
 
/* -Output-
IIF
-----
NULL*/ 

We can overcome the NULL constant issue for both the return value by using parameters. 

Why IIF over CASE and IF

Whenever we hear new things we usually get one question in mind, Why to use this new one and Why can't we use the other. Let's review one simple t-sql stqtement to acheive the same thing with IIF, CASE and IF after that we can decide which one to use. 

DECLARE @Sex BIT = 0
 
IF(@Sex = 1) 
SELECT 'MALE' AS 'Sex'
ELSE
select 'FEMALE' AS 'Sex'
GO
 
/* -Output-
Sex
------
FEMALE
*/
 
DECLARE @Sex BIT = 0
SELECT CASE WHEN @Sex=1 
THEN 'MALE'
ELSE 'FEMALE'
END AS 'Sex'
GO
 
/* -Output-
Sex
------
FEMALE
*/
 
DECLARE @Sex BIT = 0
SELECT IIF(@Sex = 1, 'MALE', 'FEMALE') AS 'Sex'
GO
 
/* -Output-
Sex
------
FEMALE
*/ 

I hope you must have got why IIF and when. Let me list down the points here,  

  • Better readability 
  • Easy to maintain
  • Very useful and handy when working with two possible outcomes based on a condition
  • Fewer lines of codes compared to other  

The fact is the IIF is translated to CASE and it's a shortway of using CASE

CHOOSE() 

The CHOOSE logical function returns the item at the specified index from a list of values. CHOOSE is a shothand way of using CASE and CHOOSE is internally a CASE statement. 

Syntax   

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

Where index is an integer expression that represents a 1-based index into the list of the items following it. If the index value is not a numeric data type then its implicitly converted to INT, If it cannot convert to an INT then a conversion error will be raised. If the index value is less than 1 or exceeds the bounds of the array of values, then CHOOSE logical function returns NULL.  The val_1 … val_n is List of comma separated values of any data type. 

DECLARE @CharIndex CHAR(1)='1'
SELECT CHOOSE (@CharIndex , 'Bronze', 'Silver', 'Glod', 'Platinum' ) AS Awards;
 
/* -Output
Awards
-------
Bronze
*/
 
DECLARE @CharValue CHAR(3)='one'
SELECT CHOOSE (@CharValue , 'Bronze', 'Silver', 'Glod', 'Platinum' ) AS Awards;
 
/*
Msg 245, Level 16, State 1, Line 2
Conversion failed when converting the varchar value 'one' to data type int.
*/
 
DECLARE @index INT=3
SELECT CHOOSE (@index , 'Bronze', 'Silver', 'Glod', 'Platinum' ) AS Awards;
 
/* -Output-
Awards
------
Glod
*/
 
SELECT CHOOSE (0 , 'Bronze', 'Silver', 'Glod', 'Platinum' ) AS Awards;
SELECT CHOOSE (5 , 'Bronze', 'Silver', 'Glod', 'Platinum' ) AS Awards;
 
/* -Output
Awards
------
NULL
*/ 

The above statements are self-descriptive to explain the CHOOSE logical function. 

CHOOSE() Usage 

Let's look into some of the CHOOSE usages in practical. 

Simple CHOOSE() 

DECLARE @index INT=3
SELECT CHOOSE (@index , 'Bronze', 'Silver', 'Glod', 'Platinum' ) AS Awards;
 
/* -Output-
Awards
------
Glod
*/ 

CHOOSE function returned the 3rd value mentioned in the value array. 

CHOOSE() with Table Data

DECLARE @BloggerScores TABLE (Name VARCHAR(100),Score FLOAT)
INSERT @BloggerScores VALUES('SNS_A',45)
INSERT @BloggerScores VALUES('SNS_B',42)
INSERT @BloggerScores VALUES('SNS_C',36)
INSERT @BloggerScores VALUES('SNS_D',29)
INSERT @BloggerScores VALUES('SNS_F',65)
INSERT @BloggerScores VALUES('SNS_G',13)

SELECT Name,Score/10 AS Rating, CHOOSE (Score/10 , 'Bronze', 'Silver', 'Glod', 'Platinum' ) AS Awards
FROM @BloggerScores;

/* -Output-
Name	Rating	Awards
----------------------
SNS_A	4.5	Platinum
SNS_B	4.2	Platinum
SNS_C	3.6	Glod
SNS_D	2.9	Silver
SNS_F	6.5	NULL
SNS_G	1.3	Bronze
*/  

I have created a Table variable and inserted some dummy data for demonstrating the CHOOSE usage with Table Data,  As mentioned above CHOOSE index will be implicitly  conevrted to INT. So in the above transact-sql, I have used Score/10 and it will be converted to INT value and accordingly the value is returned from the specified list of values by the CHOOSE logical function. 

Nested CHOOSE()  

DECLARE @Employee TABLE (Name VARCHAR(100),EmployeeLevel INT, EmployeeGrade INT)
INSERT @Employee VALUES('SNS_A',1,1)
INSERT @Employee VALUES('SNS_B',1,2)
INSERT @Employee VALUES('SNS_C',2,0)
INSERT @Employee VALUES('SNS_D',3,1)
INSERT @Employee VALUES('SNS_F',3,2)
INSERT @Employee VALUES('SNS_G',3,3)
 
SELECT Name, CHOOSE (EmployeeLevel , 
					CHOOSE(EmployeeGrade,'Senior Director','Director'), 
					'Senior Vice President', 
					CHOOSE(EmployeeGrade,'Vice President','Assistant Vice President')
			) AS EmployeeDesignation FROM @Employee;
 
/* -Output
Name	EmployeeDesignation
---------------------------
SNS_A	Senior Director
SNS_B	Director
SNS_C	Senior Vice President
SNS_D	Vice President
SNS_F	Assistant Vice President
SNS_G	NULL
*/  

CHOOSE() with CHAR value  

DECLARE @CharIndex CHAR(1)='1'
SELECT CHOOSE (@CharIndex , 'Bronze', 'Silver', 'Glod', 'Platinum' ) AS Awards;
 
/* -Output
Awards
-------
Bronze
*/
 
DECLARE @CharValue CHAR(3)='one'
SELECT CHOOSE (@CharValue , 'Bronze', 'Silver', 'Glod', 'Platinum' ) AS Awards;
 
/*
Msg 245, Level 16, State 1, Line 2
Conversion failed when converting the varchar value 'one' to data type int.
*/ 

The above statements show how CHOOSE function work with non INT data type. 

CHOOSE() with nonexistent index 

SELECT CHOOSE (0 , 'Bronze', 'Silver', 'Glod', 'Platinum' ) AS Awards;
SELECT CHOOSE (5 , 'Bronze', 'Silver', 'Glod', 'Platinum' ) AS Awards;
 
/* -Output
Awards
------
NULL
*/ 

If we are requesting something by a non existent index then CHOOSE will return NULL instead of common IndexOutOfBound erreor 

Why CHOOSE 

SELECT [DayOfWeek],
 
		CHOOSE([DayOfWeek],'Sunday','Monday', 'Tuesday', 'Wednesday','Thursday','Friday','Saturday') AS [Choose-Function],
 
		IIF([DayOfWeek] = 1, 'Sunday', 
				IIF([DayOfWeek] = 2, 'Monday', 
					IIF([DayOfWeek] = 3, 'Tuesday', 
						IIF([DayOfWeek] = 4, 'Wednesday', 
							IIF([DayOfWeek] = 5, 'Thursday', 
								IIF([DayOfWeek] = 6, 'Friday', 'Saturday'))))))
		 AS [IIF-Function],
 
		CASE [DayOfWeek]
			WHEN 1 THEN 'Sunday'
			WHEN 2 THEN 'Monday'
			WHEN 3 THEN 'Tuesday'
			WHEN 4 THEN 'Wednesday'
			WHEN 5 THEN 'Thursday'
			WHEN 6 THEN 'Friday'
			WHEN 7 THEN 'Saturday'
		END AS [Case-Function]
FROM (
	SELECT 1 AS [DayOfWeek]
	UNION
	SELECT 2
	UNION
	SELECT 3
	UNION
	SELECT 4
	UNION
	SELECT 5
	UNION
	SELECT 6
	UNION
	SELECT 7
	)DaysOfWeek
 
/*- Output-
DayOfWeek	Choose-Function	IIF-Function	Case-Function
1			Sunday			Sunday			Sunday
2			Monday			Monday			Monday
3			Tuesday			Tuesday			Tuesday
4			Wednesday		Wednesday		Wednesday
5			Thursday		Thursday		Thursday
6			Friday			Friday			Friday
7			Saturday		Saturday		Saturday
*/

The above transact-sql shows how perfect to use CHOOSE function as its more readable and maintanable.  

Performance 

Let's compare the performance of CASE, CHOOSE and IIF with the above sample code itself. [Thanks John B Oliver for suggesting this section] 

In this query I have used a table {DayOfWeek] created in the database instead of the above union function. The scripts are available with the attached code. 

CASE 

Query 

SELECT [DayOfWeek],
		CASE [DayOfWeek]
			WHEN 1 THEN 'Sunday'
			WHEN 2 THEN 'Monday'
			WHEN 3 THEN 'Tuesday'
			WHEN 4 THEN 'Wednesday'
			WHEN 5 THEN 'Thursday'
			WHEN 6 THEN 'Friday'
			WHEN 7 THEN 'Saturday'
		END AS [Case-Function]
FROM [DayOfWeek] 

Execution Plan

Compute Scalar 

CASE Compute Scalar 

CHOOSE

Query 

SELECT [DayOfWeek],
		CHOOSE([DayOfWeek],'Sunday','Monday', 'Tuesday', 'Wednesday','Thursday','Friday','Saturday') AS [Choose-Function]
FROM [DayOfWeek] 

Execution Plan 

Compute Scalar  

CHOOSE Compute Scalar 

IIF 

Query 

SELECT [DayOfWeek],

		IIF([DayOfWeek] = 1, 'Sunday', 
				IIF([DayOfWeek] = 2, 'Monday', 
					IIF([DayOfWeek] = 3, 'Tuesday', 
						IIF([DayOfWeek] = 4, 'Wednesday', 
							IIF([DayOfWeek] = 5, 'Thursday', 
								IIF([DayOfWeek] = 6, 'Friday', 'Saturday'))))))
		 AS [IIF-Function]
FROM [DayOfWeek] 

Execution Plan

Compute Scalar

Output 

The output for all the above queries will be the same as given below,

CHOOSE, IIF, CASE Output 

Performance Conclusion   

After comparing the execution plan it is clear that cost of all the queries are same and by comparing the Compute Scalar details it is clear that CHOOSE and IIF are internally translated into CASE. 

References 

Summary 

In this article I have explained SQL Server's new built-in logical functions IIF and CHOOSE with examples. If I have missed anything or need any change in definition then please let me know. I hope you have enjoyed this article and got some value addition to your knowledge.   

You might be interested in the below articles.   

I have put my time and efforts on all of my articles, Please don't forget to mark your votessuggestions and feedback to improve the quality of this and upcoming articles. Thanks for reading. 

License

This article, along with any associated source code and files, is licensed under The Code Project Open License (CPOL)

Share

About the Author

Shemeer NS
Software Developer (Senior)
India India
Technology Specialist | CodeProject MVP | Visual Studio Gallery Contributor | Author | Geek | Netizen | Husband | ChessPlayer
 
Most of my articles are listed on top 5 of the respective 'Best articles of the month' and some of my articles are published on ASP.NET WebSite's Article of the Day section.
 
Check my contributions in Visual Studio Gallery and Code Project
 
Technical Blog: http://www.shemeerns.com
Facebook: http://facebook.com/shemeernsblog
Twitter : http://twitter.com/shemeerns
Google+ : http://google.com/+Shemeernsblog
Follow on   Twitter   Google+

Comments and Discussions

 
GeneralMy vote of 5 PinprofessionalMihai MOGA13-Jul-13 20:45 
GeneralMy vote of 5 PinprofessionalPrasad Khandekar28-Jun-13 21:09 
GeneralRe: My vote of 5 PinmvpShemeer NS29-Jun-13 10:09 
GeneralRe: My vote of 5 PinprofessionalPrasad Khandekar30-Jun-13 19:51 
GeneralRe: My vote of 5 PinmvpShemeer NS30-Jun-13 20:27 
GeneralMy vote of 5 Pinmemberjhog27-Jun-13 9:57 
GeneralRe: My vote of 5 PinmvpShemeer NS27-Jun-13 10:03 
SuggestionNested IIF PinprofessionalRichard Deeming26-Jun-13 4:24 
GeneralRe: Nested IIF [modified] PinmvpShemeer NS26-Jun-13 7:35 
SuggestionChoose() example PinmemberMember 884486025-Jun-13 23:50 
GeneralRe: Choose() example PinmvpShemeer NS26-Jun-13 9:55 
QuestionAre there any performance issues? PinmemberJohn B Oliver25-Jun-13 11:43 
AnswerRe: Are there any performance issues? PinmvpShemeer NS25-Jun-13 22:03 
AnswerRe: Are there any performance issues? PinmvpShemeer NS26-Jun-13 9:50 
GeneralRe: Are there any performance issues? PinmemberJohn B Oliver2-Jul-13 11:57 

General General    News News    Suggestion Suggestion    Question Question    Bug Bug    Answer Answer    Joke Joke    Rant Rant    Admin Admin   

Use Ctrl+Left/Right to switch messages, Ctrl+Up/Down to switch threads, Ctrl+Shift+Left/Right to switch pages.

| Advertise | Privacy | Mobile
Web03 | 2.8.140814.1 | Last Updated 29 Jun 2013
Article Copyright 2013 by Shemeer NS
Everything else Copyright © CodeProject, 1999-2014
Terms of Service
Layout: fixed | fluid