SQL Server Portal

SQL Server 2012 introduced new logical function namely “IIF”. Lets discuss the syntax and purpose of this function in detail.

IIF:

Syntax :

IIF ( boolean_expression, true_value, false_value )

Purpose :
The purpose of this logical function is to check the Boolean condition and if the condition is true it will return true value otherwise false value.
This function is available in SSRS and now, it is available in SQL SERVER 2012. By using this function, you don’t need to use if else , you can validate and return value in a single line of code.

Examples :

Example-1

DECLARE @int1 int = 100;
DECLARE @int2 int = 200;
SELECT IIF ( @int1> @int2, 'TRUE', 'FALSE' ) AS Result;
--Result
FALSE

Example 2

DECLARE @int1 int = 100;
DECLARE @int2 int = 200;
SELECT IIF ( @int1> @int2, NULL, NULL ) AS Result;
--Result

Msg 8133, Level 16, State 1, Line 3</span>
At least one of the result expressions in a CASE specification must be an expression other than the NULL constant.
Reason : Because both the constant are NULL, even if one constant is not NULL it will not give this error as explained in example 3.
But, you can pass NULL parameter in both true and false value and SQL server will accept it as explained in example 4.
Example 3

DECLARE @int1 int = 100;
DECLARE @int2 int = 200;
SELECT IIF ( @int1> @int2, NULL, 'False' ) AS Result;
--Result
False

Example 4

DECLARE @int1 int = 100;
DECLARE @int2 int = 200;
DECLARE @Condition_True int = NULL;
DECLARE @Condition_False int = NULL;
SELECT IIF ( @int1> @int2, @Condition_True, @Condition_False) AS Result;
--Result
NULL

Example 5 :
Real world example of IIF function

In this example, we will enter marks in IIF condition and will get different grades as per the marks.

DECLARE @Marks int = 95;
SELECT IIF ( @Marks> 90, 'A Grade', IIF(@Marks>=80 And @Marks<=90,'B Grade','C Grade')) AS Result;
--Result
A Grade

GO
DECLARE @Marks int = 80;
SELECT IIF ( @Marks> 90, 'A Grade', IIF(@Marks>=80 And @Marks<=90,'B Grade','C Grade')) AS Result;
GO
--Result
B Grade

DECLARE @Marks int = 70;
SELECT IIF ( @Marks> 90, 'A Grade', IIF(@Marks>=80 And @Marks<=90,'B Grade','C Grade')) AS Result;
--Result
C Grade

Reference : MSDN