SQL Server  IIF() Function

Posted by

SQL Server

IIF()

Function

Example

Return “YES” if the condition is TRUE, or “NO” if the condition is FALSE:

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


Definition and Usage

The IIF() function returns a value if a condition is TRUE, or another value if
a condition is FALSE.

Syntax

IIF(

condition

,

value_if_true

,

value_if_false

)

Parameter Values

Parameter Description

condition
Required. The value to test

value_if_true
Optional. The value to return if

condition

is TRUE

value_if_false
Optional. The value to return if

condition

is FALSE

Technical Details

Works in: SQL Server (starting with 2012), Azure SQL Database

More Examples

Example

Return 5 if the condition is TRUE, or 10 if the condition is FALSE:

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

Example

Test whether two strings are the same and return “YES” if they are, or “NO” if
not:

SELECT IIF(STRCMP(“hello”,”bye”) = 0,
‘YES’, ‘NO’);

Example

Return “MORE” if the condition is TRUE, or “LESS” if the condition is FALSE:

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