Rajendra Gupta
CASE statement and IIF

Overview of SQL IIF Statement

June 12, 2019 by

SQL Server 2012 introduced a new built-in logical function SQL IIF. It is a shorthand form of writing CASE statement logic or IF-ELSE logic.

We explored Case Statement in SQL in my earlier article. We use a case statement to return a result based on the defined condition. It is similar to an IF..ELSE…THEN statement evaluates an expression and returns the output. The case statement is available in almost all versions of SQL Server.

Let’s explore this SQL IIF function in this article.

SQL IIF Statement overview

We use the following format to write Case statement logic in SQL queries

SELECT CASE Expression
When expression1 Then Result1
When expression2 Then Result2

ELSE Result
END

We can write this code using SQL IIF statement syntax as following.

IIF(boolean_expression, true_value, false_value)

  • Boolean_expression: The first parameter in SQL IIF statement is a boolean expression. It should be a valid boolean expression else we get an exception
  • True_Value: If the boolean_expression is TRUE, it returns value specified in the true_value parameter
  • False_Value: If the boolean_expression is FALSE, it returns value specified in the false_value parameter

Let’s explore SQL IIF statement using examples.

Example 1: SQL IIF statement for comparing integer values

In the following example, specified Boolean expression return False ( 2>3 is not TRUE). Due to this, it returns the value specified in the false_value parameter.

SQL IIF statement for comparing integer values

Similarly, if the condition is TRUE (5>3 is TRUE) so it returns the value of true_value parameter.

SQL IIF statement for comparing integer values - FALSE condition

Example 2: SQL IIF statement with variables

In the following example, we specified two integer variables and assigned values. We use variables for comparison in the IIF statement.

The specified condition (80>70) is TRUE, so it returns the value PASS.

IIF statement with variable

Example 3: SQL IIF statement to compare two strings

In the following example, we want to compare the string data using SQL IIF statement. We want to know the person liking based on the person name.

SQL IIF statement to compare two strings

We can specify multiple conditions in SQL IIF statement as well. Let’s define the following condition

  • Raj and Mohan both likes Apple
  • Else all other persons like orange

Specify multiple conditions

Specify multiple conditions

The query should return Likes Orange if the person name is not in the IN clause. In the following example, person Vinay does not exist in the variable @Person, so it returns value for a false clause.

Specify multiple conditions

Example 4: Nested SQL IIF statement

We can use Nested SQL IIF statement as well to specify multiple conditions

In this SQL IIF function, it checks for the first condition and if the condition is TRUE, it returns the value. In our case, it is FALSE ( Person Vinay does not match with Raj).It checks for the other condition and returns results if it is TRUE.

Nested SQL IIF statement

If none of the conditions is true, it returns the default false message.

Nested IIF statement with false condition

Example 5: SQL IIF statement with a table column

In the previous examples, we either specify values directly in the IIF statement or specified variables for it. Usually, we want to use it with the existing data in a SQL table. We can use the IIF statement with a table column as well.

In the following example, we want to know the supplier name from the PurchaseOrders table in the WideWorldImporters database.

IIF statement with a table column

If the supplier name does not exist for a particular supplier id, it returns the specified false value in SQL IIF statement.

IIF statement with a table column

Example 6: SQL IIF statement with a table column and aggregate function

Use of aggregated function with IIF

Example 7: SQL IIF statement and data type precedence

We might have different data types for the results. If we have different data types specified in the result conditions, SQL Server gives the highest precedence data type. In the following example, for the false condition(11>19), the result will be the false_value argument, i.e. 100. It should be of e integer data type, but we get the output 40.0 because the other argument float (2.6) is of high precedence than an integer.

data type precedence

You can check the data type precedence in the following image

data type precedence

Example 8: SQL IIF with NULL values

We should be careful in NULL values inside the SQL IIF function.

We cannot specify NULL in both the true and false arguments. We get the following error message.

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.

In the error message, you can see it gives information about the CASE specification however we used SQL IIF function. We will look at this in the next example.

You should specify at least one of a true or false argument.

NULL values in IIF

The similarity between SQL IIF statement and CASE Statement

In the introduction of SQL IIF Statement, we mentioned that it is a shorthand form of writing CASE statement logic or IF-ELSE logic. Yes, it is true. Internally SQL Server converts the statement into the CASE statement and executes. We can check it using the actual execution plan of a query.

Execute the query from Example 6 with an Actual execution plan.

Actual execution plan

In the actual execution plan, look at the properties of Compute Scalar. You can see it executes the CASE statement internally.

CASE statement and IIF

Conclusion

In this article, we explored the SQL IIF Statement that is a new way of writing CASE statement related conditions. It is available from SQL 2012 onwards. You should explore this to be familiar with this. If you have any comments or questions, feel free to leave them in the comments below.

Rajendra Gupta

Rajendra Gupta

Rajendra has 8+ years of experience in database administration having a passion for database performance optimization, monitoring, and high availability and disaster recovery technologies, learning new things, new features.

While working as a Senior consultant DBA for big customers and having certified with MCSA SQL 2012, he likes to share knowledge on various blogs.
He can be reached at rajendra.gupta16@gmail.com

View all posts by Rajendra Gupta
Rajendra Gupta

Latest posts by Rajendra Gupta (see all)

119 Views