IIF function in SQL Server

iif function in sql server 2012
iif function in sql server example
sql server iif function example

In this video we will discuss IIF function in SQL Server.

Healthy diet is very important both for the body and mind. If you like Aarvi Kitchen recipes, please support by sharing, subscribing and liking our YouTube channel. Hope you can help.

IIF function
Introduced in SQL Server 2012
Returns one of two the values, depending on whether the Boolean expression evaluates to true or false
IIF is a shorthand way for writing a CASE expression

Syntax : IIF ( boolean_expression, true_value, false_value )

Example : Returns Male as the boolean expression evaluates to TRUE

DECLARE @Gender INT
SET @Gender = 1
SELECT IIF( @Gender = 1, 'Male', 'Femlae') AS Gender

Example : Using IIF() function with table data. We will use the following Employees table for this example.

SQL Script to create Employees table
Create table Employees
(
Id int primary key identity,
Name nvarchar(10),
GenderId int
)
Go

Insert into Employees values ('Mark', 1)
Insert into Employees values ('John', 1)
Insert into Employees values ('Amy', 2)
Insert into Employees values ('Ben', 1)
Insert into Employees values ('Sara', 2)
Insert into Employees values ('David', 1)
Go

Write a query to display Gender along with employee Name and GenderId. We can achieve this either by using CASE or IIF.

Using CASE statement
SELECT Name, GenderId,
CASE WHEN GenderId = 1
THEN 'Male'
ELSE 'Female'
END AS Gender
FROM Employees

Using IIF function
SELECT Name, GenderId, IIF(GenderId = 1, 'Male', 'Female') AS Gender
FROM Employees

Text version of the video

Slides

All SQL Server Text Articles

All SQL Server Slides

All Dot Net and SQL Server Tutorials in English

All Dot Net and SQL Server Tutorials in Arabic

[krzysztofs]

thank you for educating community. god bless you. u are master of sql server. have a great life. best regards from Poland

[petemurphy]

Just wanted to thank you for posting this, I was working through some issues here and this was quite helpful - many thanks

[MariuszZaleski]

Awesome videos. I watch and appreciate them. One Love

[raqibulAlam-sujo]

Thanks you for sharing all your expertise to us.

[rudrashivgan]

You are The God of SQL...Hands Down...I want to donate to your channel...How Can I do so?

[artak]

It is important to know iif function, but not recommended to use it frequently because it is not standard function for sql and was created for data migration from access. Instead of iif the best practice is to use case.

[victoriawang]

I like your tutorials. would you please  make some tutorial related to sql server administering?

[shikhanikhara]

Hi Venkat,

can you please share some information on the following Topis....
1. Table Variable
2 . Temporary Table

[vivekshukla]

thank you sir
but if we have multiple case statement the how we can use iif function.

[Trzbne]

Dear Venkat,
I really appreciate your courses and usually follow your instructions in 100%. Not this time.
I am sorry but I have to disagree with you on the sample.
Your sample does not keep normal forms, and is redundant. If you change Gender table, the query with IIF will still remain the same, because it is not connected with the actual data in Gender table.
Other samples would have been better, for example take a column with a number in it and decide if it is even or odd, or evaluate if a date was earlier than a timestamp etc.

[DigishAD]

Please add the github source code link