Let's learn IIF Function in Sql Server 2012

Introduction

Sql Server 2012 has brought a lot of new functions for the T-Sql developers. In this article we will look into IIF Function.We are already familiar with IF..Else statement which executes after evaluating a Boolean value. Now from Sql 12, we have the function in place- IIF ().

Purpose

Returns a value as per the specified Boolean condition.

Syntax

IIF([Condition],[True Value],[ False Value])

Where,

Condition = Is any valid Boolean expression.

True Value = The value returned if the Condition evaluates to True.

False Value = The value returned if the Condition evaluates to False.

N.B.~ It can be treated as a shorthand version of Case statement.

Example 1: Simple IIF()

Select Result = IIF(1=1,'OK','Not OK') 

/*
	Result
	------
	OK
*/

The same can be done by using case statement as.

Select Result = Case When 1 = 1 Then 'OK'  Else 'Not OK'  End

OR using the IF..Else block as

If(1=1) Print 'OK'
Else Print 'Not OK'

Example 2: Nested IIF().Find the bigger of two numbers

Declare @Num1 As Int = 1
Declare @Num2 As Int = 2

Select Result = IIF(@Num1 > @Num2, 'First Number is bigger',
		  IIF(@Num2 > @Num1,'Second number is bigger','Numbers are equal'))

/*
	Result
	-------
	Second number is bigger
*/

However, an equivalent case statement will be

Declare @Num1 As Int = 1
Declare @Num2 As Int = 2

Select Result = Case When @Num1 > @Num2 Then 'First Number is bigger' 
			 When @Num2 > @Num1 Then 'Second number is bigger' 
			 Else 'Numbers are equal' End

Example 3: Specifying more than one condition in IIF() . Find the biggest of three numbers

We can always specify any conditional or logical operator for specifying multiple conditions inside the boolean expression as illustrated below

Declare @Num1 As Int = 10
Declare @Num2 As Int = 50
Declare @Num3 As Int = 30

Select Result = IIF(@Num1 > @Num2 And @Num1 > @Num3, 'First Number is biggest',
		  IIF(@Num2 > @Num1 And @Num2 > @Num3,'Second number is biggest',
    'Third Number is biggest'))

The equivalent case statement will be

Declare @Num1 As Int = 10
Declare @Num2 As Int = 50
Declare @Num3 As Int = 30

Select Result = Case When @Num1 > @Num2 And @Num1 > @Num3 Then 'First Number is bigger' 
			 When @Num2 > @Num1 And @Num2 > @Num3 Then 'Second number is bigger' 
			 Else 'Third Number is biggest' End

Example 4: IIF() And Choose() function

Suppose we are preparing a quiz game where every question has four options(Kindly ignore the designing of the table.Focus on the usage of Choose function). Now if we need to find out which one the user has answered, we can use this function to get the answer like the below

Declare @tblQuiz table
(  
	[QuestionID] int identity
	,[UserName] Varchar(100)	
	,[Question] Varchar(100)
	,[AnswerID] int
	,[Option1] varchar(50)
	,[Option2] varchar(50)
	,[Option3] varchar(50)
	,[Option4] varchar(50)
)
Insert into @tblQuiz Values
('Deepak','Who was the Indian Skipper when India bagged the Second World Cup Cricket Tournament?',2,'K.Dev','M.S.Dhoni','M.Singh','S.Gavaskar')
,('Deepak','Which nation got the Cricket World Cup 2011?',4,'Srilanka','England','Bangladesh','India')
,('Deepak','Who was the Man of the Tournament in  Cricket World Cup 2011?',4,'R.Singh','S.Tendulkar','Y.Singh','Z.Khan')
,('Deepak','Between which two nation the Cricket World Cup 2011 final match was played?',1,'India and Australia','India and Srilanka','WestIndies and Pakistan','Srilanka and South Africa')
Select * from @tblQuiz
/* Results truncated for spacing
QuestionID	UserName	Question	AnsID	Opt1	Opt2	Opt3	Opt4
--------	--------	----		---	----	---	--	----
1		Deepak		Who ...		2	K.Dev	M.S.Dho M.Sin	S.Gav
2		Deepak		Which...	4	Sri	Eng	Bang	India
3		Deepak		Who ...		4	R.Singh	S.Ten	Y.Singh Z.Khan
4		Deepak		Bet ...		1	Ind&Aus Ind&Sri WI&Pak Sri&SA
*/

Along with that table let us add an Answer table as under

Declare @tblAnswer table([QuestionID] int identity, [Correct Answer] varchar(50))
Insert into @tblAnswer Values('M.S.Dhoni'),('India'),('Y.Singh'),('India and Australia')

Now fire the below query

;With Cte As(
Select 
	[UserName]
	,[Question]
	,[QuestionID]
	,[Answer Given] = Choose([AnswerID],[Option1],[Option2],[Option3],[Option4])
from @tblQuiz 
)
Select c.*,Result = IIF(c.[Answer Given] = a.[Correct Answer]
   			   ,'Correct Answer','Wrong Answer')
from Cte c Join @tblAnswer a on a.QuestionID  = c.QuestionID 

/* Results truncated for spacing
UserName Question		Answer Given	Result
--------  --------		------------	------
Deepak	Who was the..		M.S.Dhoni	Correct Answer
Deepak	Which nation..		India		Correct Answer
Deepak	Who was the Man..	Z.Khan		Wrong Answer
Deepak	Between which..		India and Aus	Correct Answer
*/

How SQL Server Engine view the IIF Function?

The execution plan being generated is as under

As can be figured out that IIF gets expanded into the corresponding Case statement and that's how the operation happens

Conclusion

SQL Server 2012 (Denali) seems to be very mature and promising and has embedded with many new functions.In this article we have looked into the IIF function and it's usefulness into the T-Sql programming parlance.Hope the article will be useful.