IIF (Immediate IF) is a logical function in SQL Server that allows you to conditionally return one value or another based on a specified condition. It takes three arguments: a Boolean expression that evaluates to true or false, a value to return if the expression is true, and a value to return if the expression is false.

Syntax

The syntax for using IIF in the WHERE clause of a SQL Server query is as follows:

SELECT column1, column2
FROM table
WHERE IIF(condition, true_value, false_value) = some_value;

In this syntax, the WHERE clause is filtering the results based on the value returned by the IIF function. If the condition is true, the true_value is returned; otherwise, the false_value is returned. The returned value is then compared to the some_value.

Example

For example, let's say we have a table called "employees" with columns "employee_id" and "salary". We want to select all the employees whose salary is greater than 50000 and assign them a "high earner" status, while the rest are labeled "average earner".

SELECT employee_id,
salary,
IIF(salary > 50000, 'high earner', 'average earner') AS earner_status
FROM employees
WHERE IIF(salary > 50000, 'high earner', 'average earner') = 'high earner';

In this query, the IIF function is used twice, once in the SELECT clause to assign the earner_status, and once in the WHERE clause to filter only the high earners. The first argument of the IIF function checks if the salary is greater than 50000, and if it is, the true_value 'high earner' is returned; otherwise, the false_value 'average earner' is returned.

Using IIF in the WHERE clause can make your SQL queries more efficient and concise, as it allows you to perform conditional filtering without having to use complex CASE statements or nested queries. However, it's important to note that IIF has a performance cost, so you should use it judiciously and consider other options, such as using indexes or optimizing your database schema, if performance is a concern.