IIF

The SQL Server IIF function is a logical function introduced in SQL Server 2012 (Transact-SQL). It stands for “Immediate IF” and provides a more concise way to write a simple CASE statement with two possible outcomes.

Syntax

The syntax of the IIF function is as follows:

IIF (boolean_expression, true_value, false_value)

boolean_expression: This is the condition that you want to evaluate. If the boolean expression is true, the function returns the true_value; otherwise, it returns the false_value.

true_value: This is the value returned if the boolean expression evaluates to true.

false_value: This is the value returned if the boolean expression evaluates to false.

Example

Here’s a simple example to illustrate the usage of the IIF function. Let’s say we have a table called Employees with a column Salary, and we want to classify employees as “High” or “Low” earners based on a salary threshold:

SELECT
    EmployeeName,
    Salary,
    IIF(Salary >= 50000, 'High Earner', 'Low Earner') AS SalaryClass
FROM
    Employees;

In this example, if the salary is greater than or equal to 50000, the SalaryClass will be ‘High Earner’; otherwise, it will be ‘Low Earner’.

Before SQL Server 2012, achieving the same result would require using a CASE statement:

SELECT
    EmployeeName,
    Salary,
    CASE
        WHEN Salary >= 50000 THEN 'High Earner'
        ELSE 'Low Earner'
    END AS SalaryClass
FROM
    Employees;

While the CASE statement is more versatile and can handle more complex scenarios, the IIF function provides a more concise and readable syntax for simple conditional expressions with only two outcomes.

It’s worth noting that the IIF function is specific to SQL Server, and its usage might differ in other database management systems (DBMS). If you’re working with a different database, you should check the documentation for the equivalent function or syntax in that system.