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