SQL Server IIF Logical Function

As a database developer, you must know how to retrieve the information from the database based on the logical condition; here, I will explain SQL Server IIF logical function.

This function allows us to evaluate the condition as true and false and, based on that, returns the specified value. I will start by explaining what IIF() functions with syntax.

Then, through an example, you will understand how to use this function with simple expressions like comparing two values. Also, you will understand how the IIF() function works with NULL constants and parameters.

Finally, you will learn how to use the SQL Server IIF logical function with table and aggregate functions.

SQL Server IIF Logical Function

The SQL Server IIF logical function returns true or false based on the evaluation result. If you have an expression and want to know whether the expression is true or false, use the IIF function, which returns a value of either true or false.

The syntax is given below.

IIF( boolean_expression, true_value, false_value )

Where,

  • IIF(): It is a function that evalute the given expression and returns either true or false.
  • boolean_expression: It is the boolean expression or condition that is evaluated.
  • true_value: This value is returned if the condition becomes true.
  • false_value: This value is returned if the condition becomes false.

The return type of the IIF() function can be true or false, which of type boolean.

Let’s take an example,

Suppose you have two values, 20 and 25, and want to evaluate or compare them to check which one is bigger. You can use the IIF() function shown in the query below.

SELECT IIF( 25 > 20, '25 is greater value', '20 is smaller value');
SQL Server  IIF Logical Function

From the output, the IIF() function evaluated the expression 25 > 20 and returned the value 25 as greater. So here, the expression 25 > 20 becomes true, so the function returns the first value, ’25 is greater value’.

Take one more example, where, instead of two values, true or false, pass the null constants and see what happens.

For example, look at the query below.

SELECT IIF( 25 > 20, Null, Null);
SQL Server IIF Logical Function with Null Constants

The query returns the error from the output due to the whole statement because the Null constants are passed as the returned value.

Whenever you pass the value to the IIF() function, it results in an error, as shown in the output of the above query.

Now, instead of directly passing Null constants, if you pass the through it parameter, it returns null. To verify that, run the query below.

DECLARE @FV INT = NULL, @SV INT = NULL;  
SELECT IIF( 25 > 20, @FV, @SV);
SQL Server IIF Logical Function with Null Parameters

From the output, NULL is returned when the NULL parameters are passed the IIF() function.

Here, the first parameter or variable @FV and @SV is initialized with a NULL value, and the value of these parameters is INT.

Then, with expression 25 > 20, the @FV and @SV are passed to the IIF() function to evaluate the expression for true or false, but the value NULL is passed, so it returns the NULL value.

If you directly pass the NULL, it results in the error you have seen above.

This is how the IIF() function returns either a true or false value based on the result of the expression.

Using SQL Server IIF Logical Function with Table

Let’s take an example and see how to use SQL Server IIF logical function with table columns.

For example, you have an EmployeeSales table with columns EmployeeID, EmployeeAchieved, and EmployeeTargets, as shown below.

Using SQL Server  IIF Logical Function with EmployeeSales Table

Suppose you have to use the above table to assign the performance ratings based on sales targets achieved. You can use the IIF() function in a query, as shown below.

SELECT EmployeeID,
   	IIF(SalesAchieved >= SalesTargets, 'Exceeds Expectations', 'Meets Expectations') AS PerformanceRating
FROM EmployeeSales;
Using SQL Server  IIF Logical Function with Table

From the above output, the employee’s performance was evaluated automatically using the IIF() function, where two values are assigned: Exceeds and Meet Expectations based on the specified condition SalesAchieved >= SalesTargets.

Take on more, consider you have table Products with column ProductID, StockQuantity, and ReorderLevel.

Using SQL Server IIF Logical Function with Products Table

Suppose you work at a retail store and have access to the Products table, and have a task to identify stock levels as ‘Sufficient’ and ‘Reorder Needed’. The query below shows you can use the IIF() function to identify the stock.

SELECT ProductID,
   	IIF(StockQuantity <= ReorderLevel, 'Reorder Needed', 'Sufficient') AS InventoryStatus
FROM Products;
Using SQL Server IIF Logical Function with Products Table Identifying Stock Level

From the output, you can see each product assigned the Sufficient and Reorder Needed level based on the StockQuantity.

Here, the IIF function checks whether the stock quantity of a product falls below the reorder level and assesses the level of Reorder needed for restocking.

The expression StockQuantity <= ReorderLevel assigns the value Reorder Needed value where the stock quantity is less than or equal to the reorder level and Sufficient where the stock quantity is greater than the reorder level.

This is how to use SQL Server IIF logical function with table columns.

Using SQL Server IIF Logical Function with Aggregate Function

As you know, the aggregate function takes a list of values from the column and returns a single value; here, I will show how to use SQL Server IIF logical function with an aggregate function.

For example, you have a Sales table with columns ProductID and QuantitySold, as shown below.

Using SQL Server IIF Logical Function with Aggregate Function on Sales Table

Using the above table, suppose you have to compute the total quantity sold for each product and classify it as ‘High’ if it exceeds a certain threshold; otherwise, classify it as “Low”.

So here, you can use the IIF() function with an aggregate function called SUM(), as shown below.

SELECT
	ProductID,
	SUM(QuantitySold) AS TotalQuantitySold,
	IIF(SUM(QuantitySold) > 100, 'High', 'Low') AS SalesCategory
FROM
	Sales
GROUP BY
	ProductID;
Using SQL Server IIF Logical Function with Aggregate Function

In the above query, apply the SUM() function to compute the total quantity sold for each product.

Then, we use the IIF() function to categorize the total quantity sold as High. If it exceeds 100; otherwise, it is labelled as Low. Also, GROUP BY ProductID groups the results by each unique ProductID.

From the output, you can see ProductID with 2, 5, and 7 in the high sale category, and others are in the low sale category.

You can even use the SQL Server IIF logical function with any aggregate function, depending on how you retrieve the information from the table.

This is how to use SQL Server IIF logical function with an aggregate function in SQL Server.

Conclusion

You learned how to use the SQL Server IIF logical function in this SQL Server tutorial, where you have seen how the IIF() function returns one of those two values by evaluating the expression.

Then, you learned how to use the IIF() function with table columns and how to use it with aggregate functions like sum.

You may like to read: