IIF Function In SQL Server

Introduction

In this tutorial, I am going to explain the concept of IIF Function in SQL Server. This detailed article will cover the following topics,

1. Introduction
2. IIF Function In SQL Server
3. Points To Remember
4. Difference Between IIF Function And CASE Expression
5. Conclusion

First, let's create a database with some tables containing dummy data. Here, I am providing you with the database along with the tables containing the records, on which I am showing you the various examples. Let's see.

CREATE DATABASE OnkarSharma_OFS
PRINT 'New Database ''OnkarSharma_OFS'' Created'
GO

USE [OnkarSharma_OFS]
GO

CREATE TABLE [dbo].[Employee] (
	EmployeeID INT IDENTITY (31100,1),
	EmployerID BIGINT NOT NULL DEFAULT 228866,
	FirstName VARCHAR(50) NOT NULL,
	LastName VARCHAR(50) NOT NULL,
	Email VARCHAR(255) NOT NULL UNIQUE,
	DepartmentID VARCHAR(100) NOT NULL,
	Age INT  NOT NULL,
	GrossSalary BIGINT NOT NULL,
	PerformanceBonus BIGINT,
	ContactNo VARCHAR(25),
	PRIMARY KEY (EmployeeID)
);

CREATE TABLE [dbo].[tbl_Orders] (
	OrderId INT IDENTITY (108, 1) PRIMARY KEY,
	FoodieID INT,
	OrderStatus TINYINT NOT NULL, -- ==>> OrderStatus: 4: Cancelled; 3: Pending; 2: Processing; 1: Completed
	OrderDate DATE NOT NULL,
	ShippedDate DATE,
	RestaurantId INT NOT NULL,
);

Let's check our following tables by using the following queries.

1) To get the data from the "Employee" table, use the following query.

SELECT * FROM OnkarSharma_OFS..Employee

2) To get the data from the "tbl_Orders" table, use the following query.

SELECT * FROM OnkarSharma_OFS..tbl_Orders

IIF Function

IIF is a logical function that returns one of the two values, depending on whether the boolean expression evaluates to true or false. In simple words, the IIF() function returns "true_value" if a condition is TRUE, or "false_value" if a condition is FALSE.

Key Points

• IIF is a logical function in SQL Server.
• IIF was introduced in SQL Server 2012.
• IIF is a shorthand way for writing a CASE Expression.
• IIFs can only be nested up to a maximum level of 10.
• From the types of "true value" and "false value," the IIF function returns the data type with the highest precedence.

Syntax

IIF( boolean_expression, true_value, false_value )

• boolean_expression: If the argument is not a boolean expression, a syntax error will be thrown.
• true_value: If boolean_expression evaluates to "TRUE", it will return the value specified in the "true_value" parameter.
• false_value: If boolean_expression evaluates to "FALSE", it will return the value specified in the "false_value" parameter.

Examples

The examples in this section demonstrate the functionality of the IIF Function. Let's see.

1) IIF Function for comparing integer values

The following example will return true_value because boolean_expression is true.

SELECT IIF( 25 * 10 = 250, 'TRUE', 'FALSE' ) AS 'Result'

2) IIF Function with variables

In the following example, variables are used to calculate two integer values.

DECLARE @a INT = 25, @b INT = 12;
SELECT IIF( @a * @b = 300, 'TRUE', 'FALSE' ) AS 'Result'

3) IIF with String Functions

A) The following example accepts a string with a length greater than 10.

SELECT IIF(LEN('Hello! Vatsa') > 10, 'StringAccepted', 'StringRejected') AS [Result]

B) The following example checks the ASCII value.

SELECT IIF(ASCII('A') = 65, 'ASCIIAccepted', 'ASCIIRejected') AS [Result]

C) The following example compares string data using the IIF Function.

DECLARE @Person VARCHAR (25) = 'Onkar Sharma'
SELECT @Person + ' likes ' + IIF(@Person = 'Onkar Sharma', 'Mercedes-Benz Maybach', 'Audi A8') AS [Result]

4) IIF Function with data type precedence

SELECT IIF(21 < 11, 551.50, 551) Result

5) IIF Function with NULL

A) With NULL Constants

If we specify "NULL" in true_value and false_value, this statement will result in an error.

SELECT IIF( 25 * 12 = 300, NULL, NULL ) Result

B) With NULL Parameters

DECLARE @aa INT = NULL, @bb INT = NULL
SELECT IIF( 25 * 12 = 300, @aa, @bb ) Result

6) IIF Function With Aggregate Function

SUM()

The following example summarizes the total orders along with the order status.

SELECT    
   SUM(IIF(OrderStatus = 1, 1, 0)) AS 'Completed',  
   SUM(IIF(OrderStatus = 2, 1, 0)) AS 'Processing',  
   SUM(IIF(OrderStatus = 3, 1, 0)) AS 'Pending',  
   SUM(IIF(OrderStatus = 4, 1, 0)) AS 'Cancelled',  
   COUNT(OrderId) AS 'Total Orders'
FROM tbl_Orders
WHERE YEAR(OrderDate) = 2021

7) Nested IIF Function (with GROUP BY Clause)

The following example summarizes the total orders along with the order status.

SELECT    
   IIF(OrderStatus = 1, 'Completed', 
		IIF(OrderStatus=2, 'Processing', 
			IIF(OrderStatus=3, 'Pending', 
				IIF(OrderStatus=4, 'Cancelled', '')
				)
			)
		) AS [Order Status],
   COUNT(OrderId) AS 'Total Orders'
FROM tbl_Orders
GROUP BY OrderStatus

Points To Remember

In the key points, I have already mentioned that the IIF function is the shorthand form of the CASE Expression. And, yes, it's true. Internally, SQL Server converts IIF to CASE Expression and executes it.

Step 1

To check this, execute the following query with the "Actual Execution Plan" (Alternatively, press the "Ctrl + M" to include the Actual Execution Plan).

SELECT EmployeeID, CONCAT(FirstName , ' ' , LastName) AS [Full Name], 
	   Email, DepartmentID, GrossSalary,
	   IIF(ContactNo IS NULL, 'Not Available', ContactNo) AS [Contact Number]
FROM OnkarSharma_OFS..Employee

Step 2

Now, right-click on "Compute Scalar" and click on the "Properties" option to proceed.

Step 3

And, you can see that SQL Server converts IIF to CASE expression internally.

Difference Between IIF Function and CASE Expression In SQL Server

Now, let's look at the quick difference between IIF Function and CASE Expression in SQL Server.

Key Points	IIF Function	CASE Expression
Type	IIF is a function.	CASE is an expression.
Return Value	Returns one of two values.	Returns one of the multiple possible result expressions.
Return Type	Returns the data type with the highest precedence.	Returns the data type with the highest precedence.
Nesting	IIFs can only be nested up to a maximum level of 10.	SQL Server allows for only 10 levels of nesting in CASE expressions.
Portability	IIF is SQL Server 2012+ specific.	The CASE expression is cross-platform and works on all SQL platforms.

See you in the next article, until then take care and happy learning.

You may also visit my other articles,

• Difference Between Delete, Truncate, And Drop Statements In SQL Server
• Difference Between HAVING And WHERE Clause In SQL Server
• How To Generate Scripts For Database Objects In SQL Server
• Different Ways To Handle NULL In SQL Server

Conclusion

In this article, we have discussed the concept of IIF Function in SQL Server with various examples.

I hope you enjoyed this article. Follow C# Corner to learn more new and amazing things about SQL Server.

Thanks for reading.