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)
|
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
---- 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
|
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)
|
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