SQL Server IIF Function

Summary: in this tutorial, you will learn how to use the SQL Server IIF() function to add if-else logic to queries.

Introduction to SQL Server IIF() function #

The IIF() function accepts three arguments. It evaluates the first argument and returns the second argument if the first argument is true; otherwise, it returns the third argument.

The following shows the syntax of the IIF() function:

IIF(boolean_expression, true_value, false_value)
Code language: SQL (Structured Query Language) (sql)

In this syntax:

• boolean_expression is an expression to be evaluated. It must be a valid Boolean expression, or the function will raise an error.
• true_value is the value to be returned if the boolean_expression evaluates to true.
• false_value is the value to be returned if the boolean_expression evaluates to false.

In fact, the IIF() function is shorthand of a CASE expression:

CASE 
    WHEN boolean_expression 
        THEN true_value
    ELSE
        false_value
END
Code language: SQL (Structured Query Language) (sql)

SQL Server IIF() function examples #

Let’s take some examples of using the SQL Server IIF() function.

A) Using SQL Server IIF() function with a simple example #

This example uses the IIF() function to check if 10 < 20 and returns the True string:

SELECT 
    IIF(10 < 20, 'True', 'False') Result ;
Code language: SQL (Structured Query Language) (sql)

Here is the result:

Result
------
True

(1 row affected)Code language: PHP (php)

B) Using SQL Server IIF() function with table column example #

The following example nests IIF()function inside IIF() functions and returns the corresponding order status based on the status number:

SELECT    
    IIF(order_status = 1,'Pending', 
        IIF(order_status=2, 'Processing',
            IIF(order_status=3, 'Rejected',
                IIF(order_status=4,'Completed','N/A')
            )
        )
    ) order_status,
    COUNT(order_id) order_count
FROM    
    sales.orders
WHERE 
    YEAR(order_date) = 2018
GROUP BY 
    order_status;
Code language: SQL (Structured Query Language) (sql)

The following picture shows the output:

C) Using SQL Server IIF() function with aggregate functions #

This example uses the IIF() function with the SUM() function to get the number of orders by order status in 2018.

SELECT    
    SUM(IIF(order_status = 1, 1, 0)) AS 'Pending', 
    SUM(IIF(order_status = 2, 1, 0)) AS 'Processing', 
    SUM(IIF(order_status = 3, 1, 0)) AS 'Rejected', 
    SUM(IIF(order_status = 4, 1, 0)) AS 'Completed', 
    COUNT(*) AS Total
FROM    
    sales.orders
WHERE 
    YEAR(order_date) = 2017;
Code language: SQL (Structured Query Language) (sql)

In this example, the IIF() function returns 1 or zero if the status is matched. The SUM() function returns the number of orders for each status.

Here is the output:

In this tutorial, you have learned how to use the SQL Server IIF() function to return one of two values, based on the result of the first argument.