We are already familiar with IF..Else statement which executes after evaluating a Boolean value. Now from Sql 11 Denali (SQL Server 2012) CTP 3, 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.

Let us see this into action

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: An erroneous IIF()

Select IIF('a' > 'b', Null,Null)

Msg 8133, Level16, State 1, Line 2
At least one of the result expressions in a case specification must be an expression other than the NULL constant.

However, the below works

Declare @null sql_variant
Select IIF('a' = 'b', @null, @null)

Will result to Null

It is because of the fact that both the options of the Boolean expression results into the @null variable which has null value.

Case 5: IIF() And Choose() function

Let us once again go back to the Quiz table which we created in the last example of Choose function.

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
*/

Hope this helps