How to make a SQL "IF-THEN-ELSE" statement

Mastering SQL IF-THEN-ELSE Logic: Conditional Statements in Databases

Learn how to implement conditional logic in SQL using various IF-THEN-ELSE constructs, including CASE statements, IIF, and stored procedures, to control data flow and query results.

Conditional logic is a fundamental concept in programming and database management. In SQL, the ability to execute different actions or return different values based on specific conditions is crucial for creating dynamic and robust queries, reports, and stored procedures. This article will guide you through the various ways to implement IF-THEN-ELSE logic in SQL, focusing primarily on the widely used CASE statement, IIF function, and conditional execution within procedural blocks.

The SQL CASE Statement: Your Primary Tool for Conditional Logic

The CASE statement is the most versatile and commonly used construct for implementing IF-THEN-ELSE logic directly within SQL queries. It allows you to define different results based on multiple conditions, making your queries more flexible and readable. There are two main forms of the CASE statement: simple CASE and searched CASE.

flowchart TD
    A[Start Query] --> B{Evaluate Condition 1?}
    B -- Yes --> C[Result 1]
    B -- No --> D{Evaluate Condition 2?}
    D -- Yes --> E[Result 2]
    D -- No --> F[ELSE Result]
    C --> G[End Query]
    E --> G
    F --> G

Flowchart illustrating the logic of a SQL CASE statement.

Simple CASE Statement

A simple CASE statement compares an expression to a set of simple values. It's ideal when you're checking a single column or expression against several possible exact matches.

SELECT
    ProductName,
    UnitsInStock,
    CASE UnitsInStock
        WHEN 0 THEN 'Out of Stock'
        WHEN 1 THEN 'Low Stock'
        WHEN 2 THEN 'Low Stock'
        WHEN 3 THEN 'Low Stock'
        ELSE 'In Stock'
    END AS StockStatus
FROM Products;

Example of a simple CASE statement to categorize product stock levels.

Searched CASE Statement

A searched CASE statement evaluates a series of Boolean expressions. This form is more powerful as it allows for complex conditions, range checks, and comparisons involving multiple columns or functions. It's the equivalent of a traditional IF-THEN-ELSE IF-ELSE structure found in other programming languages.

SELECT
    OrderID,
    OrderTotal,
    CASE
        WHEN OrderTotal < 50 THEN 'Small Order'
        WHEN OrderTotal >= 50 AND OrderTotal < 200 THEN 'Medium Order'
        WHEN OrderTotal >= 200 AND OrderTotal < 1000 THEN 'Large Order'
        ELSE 'Wholesale Order'
    END AS OrderCategory
FROM Orders;

Example of a searched CASE statement to categorize orders based on their total value.

The IIF Function (SQL Server Specific)

SQL Server (since 2012) provides the IIF function as a shorthand for a simple CASE expression. It's a more concise way to write an IF-THEN-ELSE statement when you only have one condition to evaluate. The syntax is IIF(boolean_expression, true_value, false_value).

SELECT
    EmployeeName,
    Salary,
    IIF(Salary > 50000, 'High Earner', 'Standard Earner') AS SalaryCategory
FROM Employees;

Using the IIF function to categorize employees based on salary.

IF...ELSE Blocks in Stored Procedures and Functions

When working within procedural code blocks like stored procedures, functions, or triggers (especially in SQL Server's T-SQL or Oracle's PL/SQL), you can use traditional IF...ELSE control flow statements. These are used for executing different sets of SQL statements, not for returning different values within a single SELECT statement.

CREATE PROCEDURE UpdateEmployeeStatus
    @EmployeeID INT,
    @NewStatus VARCHAR(50)
AS
BEGIN
    IF @NewStatus = 'Active'
    BEGIN
        UPDATE Employees
        SET IsActive = 1, LastUpdated = GETDATE()
        WHERE EmployeeID = @EmployeeID;
        PRINT 'Employee activated.';
    END
    ELSE IF @NewStatus = 'Inactive'
    BEGIN
        UPDATE Employees
        SET IsActive = 0, LastUpdated = GETDATE()
        WHERE EmployeeID = @EmployeeID;
        PRINT 'Employee deactivated.';
    END
    ELSE
    BEGIN
        PRINT 'Invalid status provided.';
    END
END;

An IF...ELSE block in a SQL Server stored procedure to update employee status.