IIF Function In SQL Server 2012 (Code Named Denali)

A New Function IIF (Short For Inline Function) Added to SQL Server 2012. Its proivdes easiest method and alternate of writing case statements and Makes your query more Clear and readable.

Syntax :

IIF ( boolean_expression, true_value, false_value )

Take A look at following example.

This Typical A  Case Statement.

Declare @Str Varchar(30)
Set @Str='A'
Select  (Case When @Str='A' Then 'Its A' Else 'Its Not A' End) As Result

Output :

Result
---------
Its a

 

Now Take A Look How IIF can be used for above statement.

Declare @Str Varchar(30)
Set @Str='A'
Select IIF(@Str='A','Its A','Its Not A') As Result

Output :

Result
---------
Its A

Conditional Order by Clause

Create Table With Following Script

Create Table Records(
Id Int Identity(1,1),
EmpName Varchar(20),
AccountNo Varchar(12)
)
Go
 
Insert Into Records Values
('Kuldeep Bisht','012398760987'),('Vishal Verma','987026749876'),
('Rakesh Kumar','876894326754'),('Saneep Verma','786798564329'),
('Revinder Singh','999867543987'),('Seema Singh','876549879898'),
('Ashutosh','785640987567'),('Devesh Gupta','776623458123'),
('Satinder Negi','768098723456'),('Meenakshi','009812346785'),
('Manoj Pandey','767689900145'),('Sanjay Rana','980765430974')
Go

 

Now Let’s Write a Query With Conditional Order By. However Conditional Order by can also be implemented by Case Statement.

Declare @SortColumn Varchar(30)
Set @SortColumn='EmpName'
Select * from Records 
Order by IIF(@SortColumn='EmpName',EmpName,AccountNo)

Output :

So in all functionality is same as in case statement but IIF is more clear and provides readable code specially when you have nested IIF.