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

If the post helped you, please share it:
Pin It

Leave a Reply

You can use these HTML tags

<a href="" title=""> <abbr title=""> <acronym title=""> <b> <blockquote cite=""> <cite> <code class="" title="" data-url=""> <del datetime=""> <em> <i> <q cite=""> <s> <strike> <strong> <pre class="" title="" data-url=""> <span class="" title="" data-url="">