IIF() Function in SQL Server

What is IIF() Function in SQL Server?

In the SQL server, the IIF() function works like a ternary operator that evaluates an expression and returns one of the two values based on whether the expression is true or false.

Syntax of IIF() Function in SQL Server

This function has three parameters which we will discuss in the next section.

Parameters of IIF() Function in SQL Server

• boolean_expression:
  The expression to be evaluated. The result must be a boolean value otherwise the function will raise an error.
• value_if_true:
  Resulting value if the boolean_expression is truthy.
• value_if_false:
  Resulting value if the boolean_expression is falsy.

The second and the third parameters must be of compatible data types, as the function needs to return a consistent data type regardless of whether the boolean_expression is true or false.

Return Type of IIF() Function in SQL Server

The return value of the IIF() function is determined by the data type of the two possible result values provided as second and third arguments to the function.

Similarity Between IIF Function and CASE Statement in SQL Server

The IIF() function can also be expressed in the form of a CASE statement. Let's see how.

If the IIF() function statement looks like this:

The equivalent CASE statement to the above code will be:

Both the above codes have the same meaning, it's just that using the IIF() function is more concise.

Using the CASE statement will make more sense if the logic gets more complicated.

SQL Server IIF() Function Examples

1. Simple Example of SQL Server IIF() Function

In this example, we will return True if the boolean expression is true, if not, we will return False.

SQL Server Code:

Output:

The IIF() function is returning True as $1 \lt 2$.

2. Returning Number Using IIF() Function in SQL Server

In this example, we will return 5 if the boolean expression is true, otherwise, we will return 10.

SQL Server Code:

Output:

The IIF() function is returning 5 as a result because $5 \lt 10$.

3. Checking if Two Strings are the Same Using the IIF() Function in SQL Server

In this example, we will check if two strings are the same, if yes, we will return True, otherwise, we will return False.

SQL Server Code:

Output:

The IIF() function is returning Yes as the str1 and str2 strings are equal.

4. Using SQL Server IIF() Function with Table Column

Before proceeding to use the IIF() function with a table, we must create a table first.

Let's create a Students table with the following schema:

Students Schema:

Now we will create the Students table and fill it with some values.

SQL Server Code:

The resultant table will look like this:

Let's play with this table using the IIF() function in SQL Server!

We will use the IIF() function to find out who failed which exam, the passing threshold will be 40 marks.

SQL Server Code:

Output:

As we can see in the output our code has worked perfectly fine. The IIF() returned FAIL wherever the marks were less than 40 and returned Pass if the marks were more than or equal to 40.

5. Using Aggregate Function with IIF() Function in SQL Server

To save the hassle, let's use the Students table which we created in the previous example (example 4).

We will find the total number of passed students in each subject using the IIF() function and an aggregate function.

SQL Server Code:

Output:

In the above example, the IIF() function returns 1 if the marks are passing marks, otherwise, it returns 0. The SUM() is then used to count the number of 1's to find the number of students passed in each subject.

6. Using Nested IIF() Functions in SQL Server

Now we will demonstrate nested IIF() functions using the same Students table that we created in the 4th example.

In this example, we will use nested IIF() functions to categorize the average marks of each student into four categories: Excellent, Good, Average, and Poor.

SQL Server Code:

Output:

In this example, we are using a subquery to calculate the average marks for each student. After that, we use nested IIF() functions to categorize the average marks into their corresponding categories.