SQL Tutorial SQL Advanced SQL Database SQL References

SQL Server IIF() Function



The SQL Server (Transact-SQL) 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)

Parameters

condition Required. Specify the value to test.
value_if_true Required. Specify the value to return if condition is TRUE.
value_if_false Required. Specify the value to return if condition is FALSE.

Return Value

Returns value based on the condition.

Example 1:

The example below shows the usage of IIF() function.

SELECT IIF(100>200, 'TRUE', 'FALSE');
Result: 'FALSE'

SELECT IIF(100<200, 'T', 'F');
Result: 'T'

SELECT IIF(100<200, 500, 600);
Result: 500

Example 2:

Consider a database table called Sample with the following records:

Dataxy
Data 1108
Data 22022
Data 33031
Data 44039
Data 55053

The below query is used to compare records of column x and y. Based on the comparison, the result is recorded.

SELECT *, IIF(x > y, 'TRUE', 'FALSE') AS IIF_Value FROM Sample;

This will produce the result as shown below:

DataxyIIF_Value
Data 1108TRUE
Data 22022FALSE
Data 33031FALSE
Data 44039TRUE
Data 55053FALSE

❮ SQL Server Functions