SQL IIF Function

The IIF function is the new built-in Logical function introduced in SQL Server 2012. We can consider the SQL 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 in 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 IIF function:

SQL IIF Function Syntax

The syntax of the IIF logical function 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 Server IIF Comparing two integers

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

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

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

SQL Server IIF IF True or False Example 1

IIF Compare two integer variables

In this example, we will use the SQL Server 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, the condition inside it (i.e., 12 > 18) is false. So, the second statement (or third argument) returned as output
  • Within the second SELECT statement, the condition inside it (i.e., 34 > 18) is true. So, the first statement (or second argument) returned as output
SQL IIF Function 2

SQL Server IIF Compare two strings

In this example, we will use the IIF function to compare the string data. Here, we used the ‘%’ wildcard inside the second SELECT Statement. So, I suggest you refer 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;
Compare two strings Example 3

SQL Server Nested IIF example

In this example, we will use the 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 values.
  2. Within the function, the first condition verifies whether the 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 Nested IIF, we are checking one more condition here (@age >= 18 AND @age <= 60). If this condition is True, then it returns the 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 Server Nested IIF Function 4

Age = 32

Result
-----
You are eligible to Work

Age = 65

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

SQL Server IIF Working with NULL values

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

SELECT 
	IIF(10 > 5, NULL, NULL) AS Result;
SQL Server IIF function working with Null Values throwing error 8133

From the above screenshot, you can observe that it is throwing an error. Saying that at least one of the result expressions (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;
null values as arguments 8

IIF NULL values Option 2

We can overcome the SQL IIF functions errors that occurred by the NULL constants using the parameters.

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

SQL Server 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 displays 100.00. Because it will return the highest precedence data type.

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

Please refer to IF Else and CASE Statement articles.

Categories SQL

Comments are closed.