Introducing
IIF() To SQL Server
Family
IIF() is a brand new logical function introduced with SQL
Server 2012 that allows you to perform IF...THEN...ELSE conditional statements within a single function. Behavior
of this function is similar to any other programming language IIF() function!
In earlier versions of SQL Server, we have used IF...ELSE
and CASE WHEN...THEN...ELSE...END to
perform logical conditional operations.
IIF() can be handy for writing conditional CASE
statements in a single function. It evaluates the expression passed in the
first parameter and returns either TRUE or FALSE.
--Example1: Repalcement of simple IF...ELSE statement
DECLARE @GradeCHAR(1) = 'A'
SELECT IIF(@Grade = 'F', 'Failed', 'Passed') AS Result
GO
Here is the output of above T-SQL code:
Here is the output of above T-SQL code:
--Example2: Nested IIF()
DECLARE @PercentDECIMAL(5, 2) = '50'
SELECT IIF(@Percent > 80, 'A',
IIF(@Percent > 60, 'B', 'C'))
GO;
Here is the output of above T-SQL code:
----
C
(1 row(s) affected)
-- Example3: In this example, we will evaluate the marks obtained
-- by Smith and Hari and identify who got higer marks between them.
-- by Smith and Hari and identify who got higer marks between them.
DECLARE @Smith_Marks INT= 72
,@Hari_Marks INT = 86
SELECT IIF(@Smith_Marks > @Hari_Marks
,CONCAT('Smith got ',@Smith_Marks-@Hari_Marks,' marks higher than Hari')
,CONCAT('Hari got ' ,@Hari_Marks-@Smith_Marks,' marks higher than Smith')
)
GO
Here is the output:
---------------------------------------------
Hari got 14 marks higher than Smith
(1 row(s) affected)
