New Built in Function IIF in SQL Server 2012

In this article, I would like to show the new built-in function IIF in SQL Server. IIF is a shorthand way for writing a CASE statement. The IIF Function returns a single value based on the evaluation of a statement. An IIF function is mostly used in SQL stored procedures or as a formula for a particular column, which optimizes the SQL statements. So let's have a look at a practical example of how to use an IIF statement in SQL Server 2012. The example is developed in SQL Server 2012 using the SQL Server Management Studio.

We'll start by walking through a simple IIF function.

IIF Function with simple expression

The Simple IIF Function checks a Boolean value; if it is true then the True_Value is returned otherwise the False_Value is returned. The general syntax of the Simple IIF expression is:

IIF (Boolean_expression, true_value, false_value)

Example

DECLARE @Name  varchar(50)

SET @Name = 'Rohatash'

SELECT  @Name,

IIF(@Name= 'Deepak',  'Name Rohatash',  'Name Found Rohatash')
 

Output 

 

 

Now replace the name Rohatash kumar in place of Deepak.

 

Example

 

DECLARE @Name  varchar(50)

SET @Name = 'Rohatash'

SELECT  @Name,

IIF(@Name= 'Rohatash',  'Name Rohatash',  'Name Found Rohatash')

  

Output

 

 

Creating a table in SQL Server

create table student

(

stu_id int,

stu_name varchar(20),

marks int

);

Now inserting rows values into the student table:

Insert into student values('101','ravi','65');

Insert into student values('102','sumit','32');

Insert into student values('103','rekha','76');

Insert into student values('104','Yong','34');

Insert into student values('105','Hem','78'); 
 

The Student table looks like this:

 

Output
 

 

In the preceding table, the student's Id, Name and their marks are given. Now I want the following output with the help of a CASE statement and IIF Function: 

Stu_Id    Stu_Name      Marks       Remarks    
101          Ravi                65          Pass            
102          Sumit              32          Fail              
103          Rekha             76          Pass           

104          Yong              34          Pass           

105          Hem               78          Pass           

 

Using CASE Expression

 

Select Stu_Id,Stu_Name,Marks,

Case When Marks > 32 Then 'Pass' Else 'Fail'

End as Remarks

From Student

 

Output

Using IIF function with comparison

Select Stu_Id,Stu_Name,Marks,

IIF ( Marks > 32, 'Pass',  'Fail') as Remarks

From Student

 

Output

 

IIF with where condition

Select Stu_Id,Stu_Name,Marks,

IIF ( Marks > 32, 'Pass',  'Fail') as Remarks

From Student where marks< 35

 

Output

 

 

IIF with Order by clause

 

Select Stu_Id,Stu_Name,Marks,

IIF ( Marks > 32, 'Pass',  'Fail') as Remarks

From Student order by Marks

 

Output