SQL Server IIF() Function

SQL Server IIF() Function

The SQL Server IIF() function returns one of two values, depending on whether a specified condition evaluates to true or false. This function is useful for performing conditional logic within SQL queries.

Syntax

SELECT IIF(condition, true_value, false_value);

The IIF() function takes three arguments:

  • condition: The condition to evaluate.
  • true_value: The value to return if the condition is true.
  • false_value: The value to return if the condition is false.

Example SQL Server IIF() Function Queries

Let's look at some examples of SQL Server IIF() function queries:

1. Basic IIF() Example

SELECT IIF(1 < 2, 'True', 'False') AS result;

This query returns 'True' because the condition 1 < 2 is true. The result will be:

result
------
True

2. IIF() with a False Condition

SELECT IIF(1 > 2, 'True', 'False') AS result;

This query returns 'False' because the condition 1 > 2 is false. The result will be:

result
------
False

3. IIF() with Column Values

SELECT order_id, order_amount, IIF(order_amount > 1000, 'High', 'Low') AS order_category
FROM orders;

This query returns 'High' if the order_amount is greater than 1000, and 'Low' otherwise. The result will show the original order_id, order_amount, and the corresponding order_category.

4. IIF() with a Variable

DECLARE @value INT = 10;
SELECT IIF(@value > 5, 'Greater', 'Lesser') AS result;

This query uses a variable to store a value and returns 'Greater' if the value is greater than 5, and 'Lesser' otherwise. The result will be:

result
------
Greater

Full Example

Let's go through a complete example that includes creating a table, inserting data, and using the IIF() function.

Step 1: Creating a Table

This step involves creating a new table named employees to store some sample data with employee salaries.

CREATE TABLE employees (
    id INT PRIMARY KEY,
    name VARCHAR(50),
    salary DECIMAL(10, 2)
);

In this example, we create a table named employees with columns for id, name, and salary.

Step 2: Inserting Data into the Table

This step involves inserting some sample data into the employees table.

INSERT INTO employees (id, name, salary) VALUES (1, 'John Doe', 5000.00);
INSERT INTO employees (id, name, salary) VALUES (2, 'Jane Smith', 3000.00);
INSERT INTO employees (id, name, salary) VALUES (3, 'Jim Brown', 1500.00);

Here, we insert data into the employees table.

Step 3: Using the IIF() Function

This step involves using the IIF() function to categorize employees based on their salary.

SELECT id, name, salary, IIF(salary > 4000, 'High', 'Low') AS salary_category
FROM employees;

This query retrieves the id, name, salary, and the salary category for each row in the employees table. The result will be:

id  name       salary   salary_category
--- ---------- -------- ---------------
1   John Doe   5000.00  High
2   Jane Smith 3000.00  Low
3   Jim Brown  1500.00  Low

Conclusion

The SQL Server IIF() function is a powerful tool for performing conditional logic within SQL queries. Understanding how to use the IIF() function and its syntax is essential for effective data processing and manipulation in SQL Server.