In this article, I will go over the notion of the IIF Function in SQL Server. First, let's establish a database with some dummy data in it. I am supplying you with the database, as well as the tables holding the records, on which I am demonstrating the various examples. Let's see what happens.

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

USE [Peter_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 Peter_OFS..Employee

2) To get the data from the "tbl_Orders" table, use the following query.
SELECT * FROM Peter_OFS..tbl_Orders

The IIF Function
IIF is a logical function that returns one of two values based on whether the boolean expression is true or false. In other words, the IIF() method returns "true_value" if a condition is TRUE and "false_value" if it is FALSE.

Important PointsIn SQL Server, IIF is a logical function.

• SQL Server 2012 introduces IIF.
• IIF is an abbreviation for CASE Expression.
• IIFs can only be nested to a maximum of ten levels.
• The IIF function returns the data type with the highest precedence from the types "true value" and "false value."

Syntax
IIF(boolean_expression, true_value, false_value) is an IIF function.

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

Examples
The examples in this section demonstrate the IIF Function's capability. Let's see what happens.
1) The IIF function compares integer values.
Because boolean_expression is true, the next example will return true_value.

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! Scott') > 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) = 'Peter'
SELECT @Person + ' likes ' + IIF(@Person = 'Peter', '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 Peter_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.

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

HostForLIFE.eu SQL Server 2019 Hosting
HostForLIFE.eu is European Windows Hosting Provider which focuses on Windows Platform only. We deliver on-demand hosting solutions including Shared hosting, Reseller Hosting, Cloud Hosting, Dedicated Servers, and IT as a Service for companies of all sizes.