SQL Server IIF() Function

SQL Server IIF() function can be used as if-else condition in a query. It evaluates a boolean expression and returns the second expression if true else returns the third expression.

IIF(boolean_expression, true_value, false_value)

You can nested up to a maximum level of 10.

Parameters

boolean_expression: A valid Boolean expression. If the expression is not a Boolean expression, then a syntax error is raised.

true_value: The value returned by the IIF() function if the input boolean_expression evaluates to true.

false_value: The value returned by the IIF() function if the input boolean_expression evaluates to false.

Return Value

Returns a value of any data type with the highest precedence from true_value and false_value.

Note: The IIF() function is similar to a case statement.

Example 1:

In the following example, the IIF() function evaluates a boolean expression 10 < 20. Since 10 is less than 20, it retuns 'TRUE'.

SELECT IIF (10 < 20, 'TRUE' ,  'FALSE') AS Result

You can use the IIF() function with the column of a table. For example, the following uses the IIF() function with the DepartmentID column of the Employee table.

Select FirstName, LastName, IIF(DepartmentID = 10,'Accounts','Others') AS AccDept FROM Employee;

Nested IIF()

Use the IIF() with the ManagerID column from the Employee table. If the ManagerID of an employee is 1, then that employee is the VP of the company, if the ManagerID is 2, then the employee is a Director, else an employee is an Engineer.

SELECT IIF(ManagerId = 1, 'VP', IIF (ManagerId = 2, 'Director' , 'Engineer')) AS Designation FROM Employee