CASE Statement

The CASE statement in SQL Server provides the ability to perform IF-THEN-ELSE logic within a SQL statement. It allows you to return different values based on specified conditions.

Syntax

Simple CASE Expression

A simple CASE expression compares an input expression to a set of discrete values and returns a result.

CASE input_expression
               WHEN when_expression_1 THEN result_expression_1
               [ WHEN when_expression_2 THEN result_expression_2 ... ]
               [ ELSE else_result_expression ]
            END

Searched CASE Expression

A searched CASE expression evaluates a set of Boolean expressions.

CASE
               WHEN boolean_expression_1 THEN result_expression_1
               [ WHEN boolean_expression_2 THEN result_expression_2 ... ]
               [ ELSE else_result_expression ]
            END

Arguments

input_expression: The expression against which the when_expression values are compared.
when_expression: A Boolean expression that is returned when it evaluates to TRUE.
result_expression: The expression returned when the corresponding WHEN condition is met.
else_result_expression: An optional expression returned when none of the WHEN conditions are met. If ELSE is omitted and no WHEN condition is met, the CASE expression returns NULL.

Return Types

The result_expression and else_result_expression must have the same data type, or a data type that can be implicitly converted to the common data type of the result. If they are not of the same type, the data type of the result is determined by the rules of data type precedence.

Scope and Context

The CASE statement can be used in any statement or clause that accepts a valid expression, such as:

SELECT list
WHERE clause
ORDER BY clause
GROUP BY clause
HAVING clause
VALUES clause
SET clause in UPDATE statements
INSERT statements

Examples

Example 1: Categorizing Product Prices

Use a searched CASE expression to categorize products based on their list price.

SELECT
    ProductName,
    ListPrice,
    CASE
        WHEN ListPrice < 50.00 THEN 'Low Price'
        WHEN ListPrice BETWEEN 50.00 AND 100.00 THEN 'Medium Price'
        WHEN ListPrice > 100.00 THEN 'High Price'
        ELSE 'Unpriced'
    END AS PriceCategory
FROM Production.Product;

Example 2: Simple CASE for Status Mapping

Use a simple CASE expression to map status codes to descriptive names.

SELECT
    OrderID,
    CASE Status
        WHEN 1 THEN 'Pending'
        WHEN 2 THEN 'Shipped'
        WHEN 3 THEN 'Delivered'
        ELSE 'Unknown'
    END AS OrderStatus
FROM Sales.SalesOrderHeader;

Example 3: Using CASE in a WHERE Clause

Filter results based on a conditional category.

SELECT
    CustomerID,
    TotalDue
FROM Sales.SalesOrderHeader
WHERE
    CASE
        WHEN TotalDue > 1000 THEN 'High Value'
        ELSE 'Standard Value'
    END = 'High Value';

Best Practices

Always include an ELSE clause to handle unexpected values and avoid returning NULL implicitly.
Ensure all result_expression values have compatible data types.
Use meaningful aliases for the result of a CASE expression.
For complex conditions, a searched CASE expression is often more readable.