SQL Server IIF() Function
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.
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.
Returns a value of any data type with the highest precedence from true_value and false_value.
IIF() function is similar to a case statement.
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
Select FirstName, LastName, IIF(DepartmentID = 10,'Accounts','Others') AS AccDept FROM Employee;
IIF() with the
ManagerID column from 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