IIF

Output “YES” if the condition is true; otherwise, output “NO”.

SELECT IIF(500<1000, ‘YES’, ‘NO’);

Definition and Usage

The IIF() function returns one value if a condition is true, or another value if the condition is false.

IIF(condition, value_if_true, value_if_false)

Parameter	Description
Condition	Mandatory. The value that is to be evaluated or tested.
value_if_true	Optional. The value to be returned if the condition evaluates to true.
value_if_false	Optional. The value to be returned if the condition evaluates to false.

Works in:

SQL Server (from 2008 onward), Azure SQL Database, Azure SQL Data Warehouse, and Parallel Data Warehouse

Return 5 when the condition is true, or 10 when the condition is false.

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

Check if two strings are identical and return “YES” if they match, otherwise return “NO”.

SELECT IIF(‘hello’ = ‘bye’, ‘YES’, ‘NO’);

Output “MORE” if the condition holds true; otherwise, output “LESS”.

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