Hello friends, We have already explain regarding Right Join in SQL Server,left join in SQL,Self Join in SQL Server, RANK(), DENSE_RANK(), ROW_NUMBER() and Aggregate functions in sql server. Today I am going to explain regarding IIF() in SQL server. While writing CASE in sql you have to remember each CASE’s END, otherwise it will throw an error. So in simple you can use IIF() function in SQL server in place of CASE. Guys, before you start using it beware it will work only in Windows sql server 2012 or later versions.

Following is syntax for IIF():

IIF(boolean expression, true value, false value)

In IIF() function first is boolean expression means it is the condition, second parameter will be return if condition is true, otherwise third parameter will be return . It will work same as CASE but the main difference is you don’t have to remember END while writing complicated CASE in SQL server.

Following is simple example for IIF():
If i have one customer table and want result set as Boy,Girl,MEN and WOMEN on the basis of gender and Age. Following is simple query to get the result set:

CREATE TABLE [dbo].[Customer](
	[Id] [int] IDENTITY(1,1) NOT NULL,
	[CustomerName] [varchar](50) NOT NULL,
  [Gender] [varchar](5) NOT NULL,
  [Age] [INT] NOT NULL
) ON [PRIMARY]
GO

INSERT [dbo].[Customer] ([CustomerName], [Gender],[Age]) VALUES ('Test 1','M',18)
INSERT [dbo].[Customer] ([CustomerName], [Gender],[Age]) VALUES ('Test 2','F',12)
INSERT [dbo].[Customer] ([CustomerName], [Gender],[Age]) VALUES ('Test 3','M',23)
INSERT [dbo].[Customer] ([CustomerName], [Gender],[Age]) VALUES ('Test 4','F',22)
INSERT [dbo].[Customer] ([CustomerName], [Gender],[Age]) VALUES ('Test 5','F',18)
INSERT [dbo].[Customer] ([CustomerName], [Gender],[Age]) VALUES ('Test 6','F',13)
INSERT [dbo].[Customer] ([CustomerName], [Gender],[Age]) VALUES ('Test 7','M',14)
INSERT [dbo].[Customer] ([CustomerName], [Gender],[Age]) VALUES ('Test 8','M',6)
INSERT [dbo].[Customer] ([CustomerName], [Gender],[Age]) VALUES ('Test 9','M',35)

select
IIF(Gender IN ('M'),IIF(AGE<18,'Boy','MEN'),IIF(AGE<18,'Girl','WOMEN')) as 'IIF'
from Customer

If you are exited to run it. Please go through following link.
Live Demo of IIF() in SQL server

Don’t forget to share your views.
Thanks 🙂