Monday, June 6, 2016

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.

No comments:

Post a Comment

Popular Posts

Get Sponsored by Big Brands