SQL - Logical IIF() Function

This logical function was introduced with SQL Server 2012 release to cover-up the Ternary operators. Before this function, If you want to use any conditional logic then you can go through the CASE expression which is very lengthy and become more difficult for the nested condition. 

IIF is just shorthand for a simple CASE expression. It is even translated into a case statement for query optimization and execution. An IIF statement has the same query plan as the CASE expression. It is, possibly, "syntactical sugar" as initially implemented.

Syntax IIF (Boolean expression, True value, False value)  Arguments Boolean expression: A valid Boolean expression. True value: If Boolean expression evaluates to true then return value False value: If Boolean expression evaluates to false then return value Return Types Returns the data type with the highest precedence from the types in True value and False value.

If Boolean expression is true then true value will be returned else false value will be returned. We can use the nested IIF function up to the maximum level of 10.

An IIF function can be used in batches, in stored procedures, and in ad hoc queries. When this construct is used in a stored procedure, it is frequently used to test for the existence of some parameter.

NOTE: CASE function is portable across all SQL platforms whereas IIF is SQL SERVER 2012+ specific.

How does IIF function work?

As we stated earlier that IIF function works on the same way as CASE expression does. To understand IIF function, we can take the following examples-

Simple IIF expression

You can use IIF expression with the local variable to get the final output result based on the conditional expressions as given below:

----- Local variable DECLARE @inpValue INT =20 ---- SELECT ---- When Boolean Expression is true TrueResult = IIF(@inpValue=20, @inpValue, 3), ---- When Boolean Expression is false FalseResult = IIF(@inpValue<>20, @inpValue, 3)  TrueResult FalseResult 20 3

Nested IIF Expression

We can use nested IIF expression to get the result as given below:

----- Local Table Variable DECLARE @FruitMaster Table (FruitName Varchar(20)) ----- Insert data into table variable INSERT INTO @FruitMaster Values('Mango'), ('Orange'),('Apple'), ('Grapes'), ('Sapodilla') ---- Pull data SELECT FruitName from @FruitMaster FruitName Mango Orange Apple Grapes Sapodilla ---- Pull data with fruit color SELECT FruitName, ---- Nested Boolean Expression FruitColor= IIF(FruitName='Mango', 'Yellow',                                                 IIF(FruitName='Orange', 'Orange',                                                 IIF(FruitName='Apple', 'Red',                                                 IIF(FruitName='Grapes', 'Green','Brown')))) FROM @FruitMaster FruitName FruitColor Mango Yellow Orange Orange Apple Red Grapes Green Sapodilla Brown

Dynamic Query and IIF Expression

If you have a dynamic query where you want to use the conditional expressions then you can use IIF expression to get the result output as given below: 

---- Local Variable DECLARE @inpSQL Varchar(200)='' ---- Create Dynamic SQL statement with IIF SET @inpSQL = 'SELECT Cast(GETDATE() as Date) as CurrentDate, IIF(DATENAME(DW,GETDATE())=''Monday'', ''Monday'',''Unknown day'') as DayName' ---- Execute SQL EXEC (@inpSQL) CurrentDate DayName 6/6/2016 Monday

Conclusion

The Transact-SQL statement that follows an IIF expression and its condition is executed which returns one of two values, depending on whether the Boolean expression evaluates to true or false in SQL Server. It can be used to compare two strings and follow the same execution plan as CASE expression does. IIF function can be nested after another IIF.