Paladin Consultants, LLC

CASE statements are very useful for categorizing data in SQL Server queries. Consider the following SQL statement:

USE pubs
GO
SELECT 'Price Category' =
CASE
WHEN price IS NULL THEN 'Not yet priced'
WHEN price < 10 THEN 'Very Reasonable Title'
WHEN price >= 10 and price < 20 THEN 'Coffee Table Title'
ELSE 'Expensive book!'
END,
CAST(title AS varchar(20)) AS 'Shortened Title'
FROM titles
ORDER BY price
GO

This would yield a resultset like this:

One can easily see that this is much easier than writing a UNION query which would combine several individual queries selecting each price break using a WHERE clause.

SELECT Price as ‘Not Yet Priced', CAST(title AS varchar(20)) AS 'Shortened Title'
FROM titles
WHERE isnull(Price)
ORDER BY price
UNION
SELECT Price as ‘Not Yet Priced', CAST(title AS varchar(20)) AS ‘Very Reasonable'
FROM titles
WHERE Price < 10
ORDER BY price
UNIOMay 23, 2006S varchar(20)) AS 'Coffee Table Title
FROM titles
Where Price >10
ORDER BY price
GO

In a similar vein, one could use the CASE statement to create columns in a crosstab query when one knows the expected results beforehand:

USE pubs
GO
SELECT title_id,
SUM(CASE WHEN month(ord_date)between 1 and 3 THEN qty ELSE 0 END) AS Q1,
SUM(CASE WHEN month(ord_date) between 4 and 6 THEN qty ELSE 0 END) AS Q2,
SUM(CASE WHEN month(ord_date)between 7 and 9 THEN qty ELSE 0 END) AS Q3,
SUM(CASE WHEN month(ord_date)between 10 and 12 THEN qty ELSE 0 END) AS Q4
FROM sales
group by title_id, qty

This query would yield the following result:

CASE has two formats:

The simple CASE function compares an expression to a set of simple expressions to determine the result.

The searched CASE function evaluates a set of Boolean expressions to determine the result.

Both formats support an optional ELSE argument. ntax

Simple CASE function:

CASE input_expression
    WHEN when_expression THEN result_expression
        [ ... n ]
    [
        ELSE else_result_expression
    ]
END

The simple case statement allows only an equality check. If the input matches the when expression, the then clause is executed.

Searched CASE function:

CASE
    WHEN Boolean_expression THEN result_expression
        [ ... n ]
    [
        ELSE else_result_expression
    ]
END

The searched case is more powerful (but slightly slower). It allows for complicated Boolean expressions in each when clause.

CASE can be used anywhere an expression can be used: in the SELECT fields like, IN statement, WHERE clauses, and DELETE, APPEND, and UPDATE statements. The only caveat is that expressions used within the CASE statement must be of compatible data types.

Metro NY / NJ SQL Server Consultants