IIF() in 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:
 

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

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)