Students learning a new programming language will often start their first lesson with an IF statement, where their statement will return a value only if the expression is true.

They might then progress to an IF... ELSE statement, where they can determine another value to return if the expression is false. So therefore, return one value if the expression is true, and another value if it’s false.

SQL Server certainly includes the IF... ELSE statement in its T-SQL toolbox.

SQL Server also includes the IIF() function, which does a similar thing, but with a more concise syntax.

But there are some subtle differences.

The Differences

The following table outlines the main differences between the IF statement and the IIF() function.

	IF	IIF()
Type	Flow control statement.	Logical function.
How is the result determined?	You specify a SQL statement or statement block to execute.	You specify the actual value to return.
Nesting?	The limit to the number of nested levels depends on available memory.	Can be nested only up to the maximum level of 10.
What if the expression returns false?	The ELSE keyword is optional (i.e. you can choose whether or not to cater for false outcomes).	Requires both a true and a false value (i.e. you must cater for false outcomes).

The IIF() function is actually a shorthand way for writing a CASE expression. It therefore shares the same limitations as the CASE expression, which are different to the IF statement.

The IF Statement

Here’s an IF statement.

IF 1 < 2 SELECT 'True';

Result:

True

In this case, the expression to evaluate is 1 < 2. It is true that 1 is less than 2 so the SELECT statement was run and True was returned.

However, if the expression is false, nothing will happen.

IF 1 > 2 SELECT 'True';

Result:

Commands completed successfully.

All SQL Server told me is that the command completed successfully. Nothing else was returned, because nothing else was specified.

The IF… ELSE Statement

In this case, we can add the optional ELSE to the IF statement, so that it becomes an IF... ELSE statement.

IF 1 > 2
       SELECT 'True';
ELSE 
       SELECT 'False';

Result:

False

Now we also get a result when the expression is false.

The IIF() Function

The IIF() function allows us to write the same logic using a more concise syntax.

We could therefore rewrite the previous example to the following:

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

Result:

False

The IIF() function is basically a more concise way of doing the IF... ELSE statement.

Actually, that’s only partially true. As mentioned, the IIF() function is based on the CASE expression, and therefore has the same limitations of the CASE expression (such as only being able to nest to a maximum level of 10).

Therefore, the previous statement is the equivalent of the following.

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

Result:

False

Note that IIF() requires two arguments, and therefore, you are required to specify a value to return if the expression results to false (even if you specify NULL or an empty string).