IIF ( Transact–SQL)

Brief Description:      Returns one of two values, depending on whether the Boolean expression evaluates to true or false

Syntax:                       IIF ( boolean_expression, true_value, false_value )

Availability:               All Versions of SQL Server 2012

Details:                       IIF is a shorthand way for writing a CASE statement. It evaluates the Boolean expression passed as the first argument, and then returns either of the other two arguments based on the result of the evaluation. That is, the true_value is returned if the Boolean expression is true, and the false_value is returned if the Boolean expression is false or unknown. true_value and false_value can be of any type

Examples:

1.

declare @a int = 1, @b int = 2, @c int = 3, @d int = 4, @e int = 5, @f int = 6, @g int = 7

select iif ( (iif (@b > @a,@c,@d) ) < @e, @f, @g) as iifresult

go

 

iifresult

6

----------------------------------------------------------------------------------------

2. 

-- Assigning constant NULLs to true_vale , false_value  will result in error

declare @a int = 1, @b int = 2

select iif ( @a < @b , null,null)  as iifresult

go

 

Msg 8133, Level 16, State 1, Line 3

At least one of the result expressions in a CASE specification must be an expression other than the NULL constant.

-----------------------------------------------------------------------------------

 

3.

 

-- Assigning variables with NULLs to true_vale , false_value, output is NULL 

declare @a int = 1, @b int = 2, @c int = null, @d int = null

select @c = null, @d = null

 

select iif ( @a < @b , @c,@d ) as iifresult

go

 

iifresult

NULL

 

-----------------------------------------------------------------------------------

 

 

Pros:  

 

·         Shorthand for CASE when two conditions are involved

 

Cons:  

 

·         Assigning  NULL constant to true  or false value result in error

·         Assigning NULL values through variables to true or false value results in NULL output