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.
IIF (Boolean expression, True value, False value)
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
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
---- When Boolean Expression is true
TrueResult = IIF(@inpValue=20, @inpValue, 3),
---- When Boolean Expression is false
FalseResult = IIF(@inpValue<>20, @inpValue, 3)
Nested IIF Expression
We can use nested IIF expression to get the result as given below:
----- Local Table Variable
DECLARE @FruitMaster Table
----- Insert data into table variable
INSERT INTO @FruitMaster
---- Pull data
SELECT FruitName from @FruitMaster
---- Pull data with fruit color
---- Nested Boolean Expression
FruitColor= IIF(FruitName='Mango', 'Yellow',
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'')
---- Execute SQL
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.