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