In this article, we’ll explain you about SQL IIF function. IIF Stands for Immediate IF function. It is an inline IF function that can be used to provide a conditional response of a SQL statement.

It was first introduced in SQL Server 2012. Implementation of function is different than then existing SQL IF statement. Let’s start and check out below.

Introduction of SQL IIF Function:

  • IIF was first introduced in SQL Server 2012 in earlier version this is not supportive.
  • IIF is composed of the logical statement, if “Boolean” expressions condition is ‘True’ then first value will be returned else second one.
  • This is the shorthand of writing condition as compared to CASE in SQL.
  • It always returns one values depending on the Boolean expression of the condition.
-- Syntax of IIF
    IIF(boolean_expressions_condition, true, false)

IIF statement accept three parameters:

  • First is Boolean condition which contains the expression.
  • Second part will be returned in case of “true” returned by the expression. It can be Null also.
  •  Third part will be returned in case of “false” returned by the expression. It can also be Null, but both (second & third parameter) cannot be null together.

Let’s create a new table with data and try following queries using this.

CREATE TABLE [dbo].[User](
	[Uid] [int] NOT NULL,
	[FirstName] [nvarchar](50) NULL,
	[LastName] [nvarchar](50) NULL,
	[Stars] [int],
	[Gander] [nvarchar](1) NULL

INSERT [dbo].[User] ( [Uid], [FirstName], [LastName], [Stars],  [Gander]) VALUES (1,  N'Rakesh', N'kumar',  N'5',  N'M')
INSERT [dbo].[User] ( [Uid], [FirstName], [LastName], [Stars],  [Gander]) VALUES (2,  N'Mukesh', N'Kumar', N'5', N'M')
INSERT [dbo].[User] ( [Uid], [FirstName], [LastName], [Stars],  [Gander]) VALUES (3,  N'Ram', N'Singh',  N'4',  N'M')
INSERT [dbo].[User] ( [Uid], [FirstName], [LastName], [Stars],  [Gander]) VALUES (4,  N'Merry', N'smith',  N'3',  N'F')
INSERT [dbo].[User] ( [Uid], [FirstName], [LastName], [Stars],  [Gander]) VALUES (5,  N'Sham', N'Kumar', N'2',  N'M')
INSERT [dbo].[User] ( [Uid], [FirstName], [LastName], [Stars],  [Gander]) VALUES (6,  N'Thomas', N'Hawkins',  N'1',  N'M')
INSERT [dbo].[User] ( [Uid], [FirstName], [LastName], [Stars],  [Gander]) VALUES (7,  N'Garry', N'Smith',  N'3',  N'F')


How to Use ‘IIF’ and ‘FORMATMESSAGE’ in SQL:

In below example we took two numbers and pass these number variables as expression, if expression will return “true” then second part will be returned else third part (First parameter itself is expression).  

Example is as:

-- Let's declare and initialize two numbers in SQL
DECLARE @FirstNumber INT = 10;
DECLARE @SecondNumber INT = 20

SELECT IIF( (@SecondNumber > @FirstNumber), FORMATMESSAGE('%i is a greater no. than %i', @SecondNumber, @FirstNumber),
                             FORMATMESSAGE('%i is a greater no. than %i help %i', @FirstNumber, @SecondNumber, 11))
                             AS [NumbersTest];


SQL IIF Function


In above example we have used “FORMATMESSAGE” function. As name stats this function is used to format the message. It accepts multiple parameters, very first parameter can contain constant string, variables and it will have “%i” in it which will be replaced with the value of next parameters. In above example we have taken two parameters (@SecondNumber, @FirstNumber) and here are only two occurrences of “%i”  in string. Its syntax is


FORMATMESSAGE ( { msg_number  | ' msg_string ' | @msg_variable} , [ param_value [ ,...n ] ] ) 

Another example of IIF:

In similar case we can also use this to display Gander, Marital Status etc. within the same SQL query, let’s check out below

SELECT FirstName,LastName, IIF(Gander = 'M', 'Male', 'Female') as [Gander] FROM [User]


SQL IIF Function

Nesting of IIF in same query:

Let’s discuss new scenario in which we will do the nesting of SQL IIF functions. Here we have the rating in numbers when we want to return readable text form the query. So, we’ll write the query as 

SELECT FirstName,
       IIF(Stars = '1', 'Very Poor',
       IIF(Stars = '2', 'Poor',
       IIF(Stars = '3', 'Average',
       IIF(Stars = '4', 'Good',
       IIF(Stars = '5', 'Excellent', 'n/a'))))) AS [User_Rating] FROM [User]


SQL IIF Function

As stated above beside expression (i.e., first parameter) next two cannot be null to gather. It will give error as below  

 SELECT FirstName,LastName, IIF(Gander = 'M', null, null) as [Gander] FROM [User]
SQL IIF Function

We hope above article has given you enough understanding of IIF function and you’ll able to use this in your daily development practices. You can also check out Microsoft Docs.

For more knowledge of MS SQL, you can check this section HERE

Leave a Reply

Your email address will not be published. Required fields are marked *

Explore More

How to enable SQL Server authentication

SQL Server Authentication is a default way to connect to MS SQL Server, but there may be many times that you need to use SQL Server Authentication to connect to

Set Transaction Isolation Level in SQL 

In this post we’ll discuss SQL server “Set Transaction Isolation Level”. These are of 5 types, and we’ll discuss all one by one below. READ UNCOMMITTED Read uncommitted specifies that

Is SQL NOLOCK bad practice?

In this post we’ll read about the use of [Nolock] in SQL Queries and stored procedures. Is this good or bad practice to use this in query. Let’s check out