SQL IIF Function

The SQL IIF function is the new built-in Logical function introduced in SQL Server 2012. We can consider the SQL Server IIF as the shorthand way of writing IF Else, and CASE statements.

SQL Server IIF function will accept three arguments. The first argument is the Boolean expression, which returns true or false. If the IIF expression results TRUE, then the second argument will be returned as a result. Otherwise, the third argument will return as output. Let us see the syntax of the SQL Server IIF function:

SQL IIF Logical Function Syntax:

The syntax of the IIF in SQL Server is as follows:

IIF (Boolean_Expression, True_Value, Flase_Value)

If the given Boolean expression (or test condition) is true, then it will return True_Value. And if it is false, then False_Value will be returned.

SQL IIF Comparing two integers

This example will show you the working functionality of IIF function in SQL Server

SELECT 
	IIF(10 > 5, 'TRUE', 'FALSE') AS Result;

From the below screenshot, you can observe that Condition inside IIF function (i.e., 10 > 5) is TRUE. So, the first statement (or second argument) returned as output, which is TRUE

SQL IIF Function 1

SQL IIF Compare two integer variables

In this example, we are going to use the IIF function to find whether the person is eligible to vote or not.

DECLARE @John_age INT = 12,
	@Dave_age INT = 34, 
	@minimum INT = 18  
SELECT IIF ( @John_age > @minimum, 'Eligible to Vote', 'Not Eligible to Vote' ) AS Result1;  

SELECT IIF ( @Dave_age > @minimum, 'Eligible to Vote', 'Not Eligible to Vote' ) AS Result2;

From the below screenshot you can observe that,

  • Within the first SELECT statement, condition inside IIF function (i.e., 12 > 18) is false. So, the second statement (or third argument) returned as output
  • Within the second SELECT statement, condition inside the IIF function (i.e., 34 > 18) is true. So, the first statement (or second argument) returned as output
SQL IIF Function 2

SQL IIF Compare two strings

In this example, we are going to use the SQL Server IIF function to compare the string data. Here, we used the ‘%’ wildcard inside the second SELECT Statement. So, I suggest you refer SQL LIKE article in SQL Server for further reference.

DECLARE @name AS VARCHAR(50) = 'Tutorial Gateway'
SELECT 
	IIF (@name = 'Tutorial Gateway', 'TRUE', 'FALSE') AS Result1;

SELECT 
	IIF (@name LIKE N'Tutorial%', 'TRUE', 'FALSE') AS Result2;

SELECT 
	IIF (@name = N'Tutorial', 'TRUE', 'FALSE') AS Result3;
SQL IIF Function 3

SQL Nested IIF example

In this example, we are going to use the Sql Server Nested IIF function to find whether the person is eligible to work or not.

DECLARE @age INT = 12

SELECT IIF ( @age < 18, 
	     'You are too Young to Work', 
	      IIF(@age >= 18 AND @age <= 60, 
		  'You are eligible to Work', 
		  'You are too Old to Work'
		  )
	    ) AS Result;
  1. First, we declared an integer variable called @age and assigned some value.
  2. Within the IIF() function, the first condition verifies whether age is less than 18. If this condition is True, then it will return the first statement (or second argument) after the comma, which is You are too Young to Work
  3. When the first condition fails, it returns the second statement (or third argument). By using SQL Server Nested IIF, we are checking one more condition here (@age >= 18 AND @age <= 60). If this condition is True, then it returns first value after the comma, which is You are eligible to Work
  4. If the Nested condition fails, it will execute the value at the third argument, which is You are too Old to Work.

OUTPUT 1: Age = 12

SQL IIF Function 4

Age = 32

Result
-----
You are eligible to Work

Age = 65

Result
-----
You are too Old to Work

SQL IIF Working with NULL values

It would be best if you were careful while you are working with NULL values inside the IIF function. Following series of examples will help you to understand the consequences.

SELECT 
	IIF(10 > 5, NULL, NULL) AS Result;
SQL IIF Function 7

From the above screenshot, you can observe that it is throwing an error. Saying that at least one of the result expression (i.e., second or third argument after the Boolean_Expression) must be an expression other than the NULL constant. Let us change them accordingly and see

SELECT 
	IIF(10 > 5, 'TRUE', NULL) AS Result1;

SELECT 
	IIF(10 > 5, NULL, 'FALSE') AS Result2;
SQL IIF Function 8

SQL IIF NULL values Option 2

We can overcome the errors that occurred by the NULL constants in the IIF function using the parameters.

DECLARE @a INT = NULL,
        @b INT = NULL
		 
SELECT IIF ( 10 > 5, @a, @b ) AS Result1;

Output

Result1
-------
NULL

IIF Data type with the highest precedence

The IIF function will return the data type with the highest precedence. From the following example, the result will be the third argument (i.e., 100) of type integer, but the result is displaying 100.00. Because the IIF function will return the highest precedence data type.

SELECT 
	IIF ( 15 > 50, 12.45, 100 ) AS Result;

Output

Result
------
100.00

Please refer IF Else and CASE Statement articles.

Comments are closed.