In SQL Server, the IIF() function (not to be confused with the IF statement) is a conditional function that returns the second or third argument based on the evaluation of the first argument.

It’s a shorthand way for writing a CASE expression. It’s logically equivalent to CASE WHEN X THEN Y ELSE Z END assuming IIF(X, Y, Z).

IIF() is an abbreviation for Immediate IF.

Syntax

The syntax goes like this:

IIF ( boolean_expression, true_value, false_value )

Where:

• boolean_expression is the expression to be evaluated.
• true_value is returned if boolean_expression is true.
• false_value is returned if boolean_expression is false or unknown.

As with CASE expressions, IIF() statements can be nested only up to the maximum level of 10.

Example

Here’s a basic example to demonstrate how IIF() works.

SELECT IIF( 1 < 2, 'True', 'False' );

Result:

True

In this case, the expression to evaluate is 1 < 2. It is true that 1 is less than 2 so the second argument was returned.

This is the equivalent of doing the following.

SELECT 
  CASE WHEN 1 < 2 THEN 'True'
    ELSE 'False'
  END;

Result:

True

In these examples I used the words “True” and “False” but I could have used anything.

For example, I could have done this instead:

SELECT IIF( 1 < 2, 'Fail', 'Pass' );

Result:

Fail

Or it could be something completely removed from a “true” or “false” binary-type answer.

For example:

SELECT IIF( 1 < 2, 'Deadlift', 'Bench Press' );

Result:

Deadlift

Testing a Variable

In practice, you’d normally test variables, columns, etc rather than constants like in the previous examples.

Here’s an example that tests a variable.

DECLARE @bankBalance int = 123;
SELECT IIF(@bankBalance > 100, 'Rich!', 'Poor');

Result:

Rich!

Database Example

Here’s an example that uses columns from a database.

SELECT TOP(10)
    Name,
    Population,
    IIF( 
        Population > 400000, 
        'Big City', 
        'Small City' 
        ) AS 'Big/Small'
FROM city;

Result:

+----------------+--------------+-------------+
| Name           | Population   | Big/Small   |
|----------------+--------------+-------------|
| Kabul          | 1780000      | Big City    |
| Qandahar       | 237500       | Small City  |
| Herat          | 186800       | Small City  |
| Mazar-e-Sharif | 127800       | Small City  |
| Amsterdam      | 731200       | Big City    |
| Rotterdam      | 593321       | Big City    |
| Haag           | 440900       | Big City    |
| Utrecht        | 234323       | Small City  |
| Eindhoven      | 201843       | Small City  |
| Tilburg        | 193238       | Small City  |
+----------------+--------------+-------------+

Nested IIF() Functions

Here’s an example of nesting an IIF() function. By “nesting” I mean that I pass another IIF() function as an argument to the outer IIF() function.

DECLARE @bankBalance int = 123;
SELECT IIF(
    @bankBalance > 100, 
    IIF(@bankBalance > 150, 'Rich!', 'Wealthy'), 
    'Poor'
    );

Result:

Wealthy

You can nest IIF() functions up to a level of 10.