SQL IIF Function

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
) 
GO  

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')

GO

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

--OUTPUT 
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];

Output:

NOTE: 

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]

Output:

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,
       LastName,
       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]

   Output:

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]

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