Built-in Logical Function IIF in SQL Server

IIF is a built-in logical function introduced in SQL Server 2012.

The function takes three arguments. The first must be a Boolean expression, and depending on whether the expression evaluates to true or false, the function returns the second or the third argument.

If the first parameter is a not a Boolean expression, like in the next example:

SELECT IIF ( 1, 'TRUE', 'FALSE' ) AS 'IIF Result'

...you will get an error:

IIF_error

Msg 4145, Level 15, State 1, Line 1
An expression of non-boolean type specified in a context where a condition is expected, near '('.

Here is a simple example how the IIF function works:

SELECT IIF ( 1 < 2, 'TRUE', 'FALSE' ) AS 'IIF Result';

IIF_result

The following example uses [HumanResources].[Employee] table in AdventureWorks database and checks whether values in VacationHours column are lower than 40:

SELECT BusinessEntityID
,VacationHours
, IIF ( VacationHours < 40, 'Low', 'High' ) FROM [AdventureWorks].[HumanResources].[Employee]

IIF_result_adventureworks

Boolean expression can check for NULL values:

DECLARE @N VARCHAR(1)
SELECT IIF(@N IS NULL,'TRUE', 'FALSE') AS 'Null IIF Result'

IIF_null_result

IIF statements can be nested:

SELECT IIF ( 1 < 2, IIF ( 2<3, 'a', 'b' ), 'c' ) AS 'Nested IIF Result';

IIF_nested_result