SQL Server IIF() Function

The IIF() function in SQL Server is a simple way to add conditional logic to your queries. It lets you return different values based on whether a condition is true or false, similar to an if-then-else statement in programming.

IIF(): Definition and Usage

IIF() is a very useful function for adding decision-making to your SQL statements. It takes a condition, a value to return if the condition is true, and a value to return if the condition is false. This makes it easy to create more dynamic and flexible queries.

Syntax


IIF(condition, value_if_true, value_if_false)

Parameter Values

Parameter	Description
`condition`	The condition to evaluate (must result in TRUE or FALSE). This is required.
`value_if_true`	The value to return if the condition is TRUE. This is optional.
`value_if_false`	The value to return if the condition is FALSE. This is optional.

Examples

Returning Strings Based on a Condition

This example returns "YES" if 500 is less than 1000, and "NO" otherwise.

Syntax


SELECT IIF(500 < 1000, 'YES', 'NO');

Output

YES

Returning Numbers Based on a Condition

This returns 5 if the condition is true, and 10 otherwise.

Syntax


SELECT IIF(500 < 1000, 5, 10);

Output

Comparing Strings

This compares two strings and returns "YES" if they are equal, "NO" otherwise.

Syntax


SELECT IIF('hello' = 'bye', 'YES', 'NO');

Output

NO

Conditional Output in a Query

This example adds a column ('Result') to the output based on the 'Quantity' in the 'OrderDetails' table (assuming an 'OrderDetails' table with 'OrderID' and 'Quantity' columns exists).

Syntax


SELECT OrderID, Quantity, IIF(Quantity > 10, 'MORE', 'LESS')
FROM OrderDetails;

Output


OrderID | Quantity | Result
---------------------------
(OrderID and Quantity from OrderDetails table) | (MORE if Quantity>10, LESS otherwise)