SQL Server provides the CASE expression, which allows you to perform conditional logic within your queries. CASE expressions can be used in various parts of a SQL statement, including the SELECT list, WHERE clauses, HAVING clauses, IN lists, DELETE and UPDATE statements, and even inside built-in functions.

Simple CASE Expressions

A simple CASE expression checks one expression against multiple values. It allows only an equality check, meaning it compares the first expression to the expression in each WHEN clause for equivalency. If a match is found, the expression in the corresponding THEN clause is returned. If no match is found, the expression in the ELSE clause is returned.

Here’s an example of a simple CASE expression:

DECLARE @TestVal INT
SET @TestVal = 3

SELECT CASE @TestVal
    WHEN 1 THEN 'First'
    WHEN 2 THEN 'Second'
    WHEN 3 THEN 'Third'
    ELSE 'Other'
END

In this example, the value of @TestVal is 3. The CASE expression checks if @TestVal matches any of the specified values (1, 2, or 3). Since it matches 3, the expression ‘Third’ is returned.

Searched CASE Expressions

A searched CASE expression allows the use of comparison operators and Boolean expressions. It checks each Boolean expression in the WHEN clauses and returns the expression in the corresponding THEN clause of the first matching condition. If no condition is met, the expression in the ELSE clause is returned.

Here’s an example of a searched CASE expression:

DECLARE @TestVal INT
SET @TestVal = 5

SELECT CASE
    WHEN @TestVal <= 3 THEN 'Top 3'
    ELSE 'Other'
END

In this example, the value of @TestVal is 5. The searched CASE expression checks if @TestVal is less than or equal to 3. Since it is not, the expression ‘Other’ is returned.

Both simple and searched CASE expressions are powerful tools in SQL Server that allow you to perform conditional logic and make your queries more flexible. By understanding how to use CASE expressions effectively, you can enhance the functionality and readability of your SQL code.