SQL Server IIF() Function

In SQL Server, the IIF() function is a conditional function used to return different values based on specific conditions. It can conveniently replace the CASE statement and has a simple and understandable syntax, making it very suitable for use in SQL queries.

Syntax

The syntax of the IIF() function is as follows:

IIF (condition, true_value, false_value)

where:

• condition is the condition to be tested.
• true_value is the value to be returned when condition is true.
• false_value is the value to be returned when condition is false.

Usage

The IIF() function is typically used in queries when different values need to be returned based on specific conditions, such as:

• Returning different text or numbers based on a boolean condition.
• Returning different aggregate function results based on a condition.

Examples

Here are two examples of using the IIF() function.

Example 1: Returning Different Text

Suppose we have a table containing product names, prices, and discounts. We want to query the actual price of each product, which is the price after the discount is applied, or the original price if there is no discount. We can use the following SQL statement:

SELECT ProductName, Price, Discount,
       IIF(Discount IS NULL, Price, Price * (1 - Discount)) AS ActualPrice
FROM Products

The above query uses the IIF() function to test if the discount is null. If it is null, it returns the original price, otherwise, it returns the price after the discount is applied. Here is an example output:

Example 2: Returning Different Aggregate Function Results

Suppose we have a table containing order information, including order number, customer ID, and order date. We want to query the number of orders and the earliest order date for each customer. We can use the following SQL statement:

SELECT CustomerID,
       COUNT(*) AS OrderCount,
       IIF(MIN(OrderDate) IS NULL, 'N/A', CONVERT(VARCHAR(10), MIN(OrderDate), 120)) AS EarliestOrderDate
FROM Orders
GROUP BY CustomerID

The above query uses the IIF() function to test if the earliest order date is null. If it is null, it returns “N/A”, otherwise, it returns the earliest order date in string format. Here is an example output:

Conclusion

The IIF() function is a very useful conditional function that can conveniently perform different operations based on conditions. Its syntax is simple and understandable, and it has a wide range of uses. In situations where query results need to be dynamically generated based on conditions, the IIF() function can greatly simplify code writing and improve query efficiency. In practical applications, we can choose the appropriate conditional function based on specific needs to improve query efficiency and accuracy.