Using CASE Expressions in SQL Server

CASE expressions are a powerful tool in SQL Server that allow you to perform conditional logic within your queries. They can be used to evaluate an expression and return a value based on certain conditions, or to evaluate a set of Boolean expressions to find the result.

There are two formats for CASE expressions: simple and searched. The simple format compares an expression to a set of simple expressions to find the result, while the searched format evaluates a set of Boolean expressions to find the result.

Simple CASE Expression

The simple CASE expression has the following syntax:

CASE expression
    WHEN expression1 THEN result1
    WHEN expression2 THEN result2
    ELSE resultN
END

This expression compares an expression to a set of simple expressions to find the result. The data types of the input expression and each when expression must be the same or must be an implicit conversion.

For example:

SELECT 
    FirstName, 
    State = CASE StateCode
        WHEN 'MP' THEN 'Madhya Pradesh'
        WHEN 'UP' THEN 'Uttar Pradesh'
        WHEN 'DL' THEN 'Delhi'
        ELSE NULL
    END,
    PayRate
FROM 
    dbo.Customer;

Searched CASE Expression

The searched CASE expression has the following syntax:

CASE
    WHEN Boolean_expression1 THEN result1
    WHEN Boolean_expression2 THEN result2
    ELSE resultN
END

This expression evaluates a set of Boolean expressions to find the result. This expression allows comparison operators and logical operators AND/OR within each Boolean expression.

For example:

SELECT 
    FirstName, 
    State = CASE 
        WHEN StateCode = 'MP' THEN 'Madhya Pradesh'
        WHEN StateCode = 'UP' THEN 'Uttar Pradesh'
        WHEN StateCode = 'DL' THEN 'Delhi'
        ELSE NULL
    END,
    PayRate
FROM 
    dbo.Customer;

Using CASE Expressions in Different Clauses

CASE expressions can be used within various clauses, including:

• SELECT statement: to evaluate an expression and return a value based on certain conditions.
• WHERE clause: to filter data based on certain conditions.
• ORDER BY clause: to sort data based on certain conditions.
• HAVING clause: to filter grouped data based on certain conditions.
• UPDATE statement: to update data based on certain conditions.

For example:

-- Using CASE expression in SELECT statement
SELECT 
    FirstName, 
    State = CASE StateCode
        WHEN 'MP' THEN 'Madhya Pradesh'
        WHEN 'UP' THEN 'Uttar Pradesh'
        WHEN 'DL' THEN 'Delhi'
        ELSE NULL
    END,
    PayRate
FROM 
    dbo.Customer;

-- Using CASE expression in UPDATE statement
UPDATE 
    Customer
SET 
    StateCode = CASE StateCode
        WHEN 'MP' THEN 'Madhya Pradesh'
        WHEN 'UP' THEN 'Uttar Pradesh'
        WHEN 'DL' THEN 'Delhi'
        ELSE NULL
    END;

-- Using CASE expression in ORDER BY clause
SELECT 
    *
FROM 
    dbo.Customer
ORDER BY 
    CASE Gender 
        WHEN 'M' THEN FirstName 
    END DESC,
    CASE Gender 
        WHEN 'F' THEN LastName 
    END ASC;

Common Pitfalls

One common pitfall when using CASE expressions is to use the OR operator within a simple CASE expression. For example:

CASE ebv.db_no 
    WHEN 22978 OR 23218 OR 23219 THEN 'WECS 9500'
    ELSE 'WECS 9520'
END AS wecs_system

This will result in an error, as the OR operator is not supported within a simple CASE expression. Instead, you can use the searched CASE expression:

CASE 
    WHEN ebv.db_no = 22978 OR ebv.db_no = 23218 OR ebv.db_no = 23219 THEN 'WECS 9500'
    ELSE 'WECS 9520'
END AS wecs_system

Alternatively, you can use the IN operator:

CASE 
    WHEN ebv.db_no IN (22978, 23218, 23219) THEN 'WECS 9500'
    ELSE 'WECS 9520'
END AS wecs_system

In conclusion, CASE expressions are a powerful tool in SQL Server that can be used to perform conditional logic within your queries. By understanding the simple and searched formats, as well as how to use them in different clauses, you can write more efficient and effective queries.