CASE Expression

The CASE expression allows SQL expressions to be simplified for conditional values. The CASE expression in SQL Server 6.0 is ANSI SQL-92-compliant and allowed anywhere an expression is used.

Syntax

Simple CASE expression:

CASE expression
    WHEN expression1 THEN expression1
    [[WHEN expression2 THEN expression2] [...]]
    [ELSE expressionN]
END

Searched CASE expression:

CASE
    WHEN Boolean_expression1 THEN expression1
    [[WHEN Boolean_expression2 THEN expression2] [...]]
    [ELSE expressionN]
END

CASE-related functions:

COALESCE (expression1, expression2)
COALESCE (expression1, expression2, ...expressionN)
NULLIF (expression1, expression2)

where

expression: Is a constant, column name, function, subquery, and any combination of arithmetic, bitwise, and string operators.
In a simple CASE expression, the first expression is compared to the expression in each WHEN clause for equivalency. If these expressions are equivalent, the expression in the THEN clause will be returned.
Boolean_expression: Determines whether to use the THEN clause. The searched CASE expression allows comparison operators, and the use of AND and/or OR between each Boolean_expression. A simple CASE expression does not. The simple CASE expression checks only for equivalent values.
ELSE expressionN: Specifies the result to be returned when all other WHEN clauses fail. ELSE expressionN is optional. When not included, if all other WHEN clauses fail, CASE returns NULL.
COALESCE (expression1, expression2): Is equivalent to a searched CASE expression where a NOT NULL expression1 returns expression1 and a NULL expression1 returns expression2. In searched CASE expression form, it would look like this:

CASE
    WHEN expression1 IS NOT NULL THEN expression1
    ELSE expression2
END

COALESCE (expression1, expression2, ... expressionN): Is equivalent to the COALESCE function where each of the expressions return that expression when the value is NOT NULL. A NULL expressionN will return the first non-null expressionN in the list. If no non-null values are found, CASE returns NULL. In searched CASE statement form, it would look like this:

CASE
    WHEN value_expression1 IS NOT NULL THEN value_expression1
    ELSE COALESCE(value_expression2, ... value_expressionN)
END

NULLIF (expression1, expression2): Is equivalent to a searched CASE expression where expression1 = expression2 and the resulting expression is NULL. In searched CASE expression form, it would look like this:

CASE
    WHEN expression1=expression2 THEN NULL 
    ELSE expression1
END

Remarks

All datatypes used for the replacement expression in the THEN clause must include compatible datatypes. This table shows compatible and resulting datatypes.



Datatypes in THEN expressions	Resulting datatype
Mixed.	If the datatype used is not compatible (implicit conversion not supported by SQL Server), an error will occur.
Combination of fixed-length char with lengths cl1, cl2, and cl3.	Fixed-length char with length equal to the greater of cl1, cl2, and cl3.
Combination of fixed-length binary with lengths bl1, bl2, and bl3.	Fixed-length binary with length equal to the greater of bl1, bl2, and bl3.
Combination of fixed and variable-length char.	Variable-length char with length equal to the maximum-length expression.
Combination of fixed and variable-length binary.	Variable-length binary with length equal to the maximum-length expression.
Combination of numeric datatypes (for example, smallint, int, float, money, numeric, and decimal).	Datatype equal to the maximum precision expression. For example, if one expression resulted in an int and another in a float, the resulting datatype would be float, because float is more precise than int.

Examples

A. SELECT Statement with a Simple CASE Expression

Within a SELECT statement, a simple CASE expression allows only an equality check; no other comparisons are made. In this example, the CASE expression is used to alter the display of book categories to make them more understandable.

SELECT     Category = 
        CASE type
            WHEN 'popular_comp' THEN 'Popular Computing'
            WHEN 'mod_cook' THEN 'Modern Cooking'
            WHEN 'business' THEN 'Business'
            WHEN 'psychology' THEN 'Psychology'
            WHEN 'trad_cook' THEN 'Traditional Cooking'
            ELSE 'Not yet categorized'
        END, 
    "Shortened Title" = CONVERT(varchar(30), title), 
    Price = price
FROM titles
WHERE price IS NOT NULL
ORDER BY type
COMPUTE AVG(price) BY type
go

Category            Shortened Title                Price
------------------- ------------------------------ -----------
Business            Cooking with Computers: Surrep 11.95
Business            Straight Talk About Computers  19.99
Business            The Busy Executive's Database  19.99
Business            You Can Combat Computer Stress 2.99

                                                   avg
                                                   ===========
                                                   13.73

Category            Shortened Title                Price
------------------- ------------------------------ -----------
Modern Cooking      Silicon Valley Gastronomic Tre 19.99
Modern Cooking      The Gourmet Microwave          2.99

                                                   avg
                                                   ===========
                                                   11.49

Category            Shortened Title                Price
------------------- ------------------------------ -----------
Popular Computing   But Is It User Friendly?       22.95
Popular Computing   Secrets of Silicon Valley      20.00

                                                   avg
                                                   ===========
                                                   21.48

Category            Shortened Title                Price
------------------- ------------------------------ -----------
Psychology          Computer Phobic And Non-Phobic 21.59
Psychology          Emotional Security: A New Algo 7.99
Psychology          Is Anger the Enemy?            10.95
Psychology          Life Without Fear              7.00
Psychology          Prolonged Data Deprivation: Fo 19.99

                                                   avg
                                                   ===========
                                                   13.50

Category            Shortened Title                Price
------------------- ------------------------------ -----------
Traditional Cooking Fifty Years in Buckingham Pala 11.95
Traditional Cooking Onions, Leeks, and Garlic: Coo 20.95
Traditional Cooking Sushi, Anyone?                 14.99

                                                   avg
                                                   ===========
                                                   15.96

(21 row(s) affected)

B. SELECT Statement with Simple and Searched CASE Expressions

Within a SELECT statement, the searched CASE expression allows values to be replaced in the results set based on comparison values. In this example, the price (a money column) is displayed as a text comment based on ranges of cost for the books. It is important that all possibilities are checked.

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,
    "Shortened Title" = CONVERT(varchar(20), title),
    Category = 
        CASE type
            WHEN 'popular_comp' THEN 'Popular Computing'
            WHEN 'mod_cook' THEN 'Modern Cooking'
            WHEN 'business' THEN 'Business'
            WHEN 'psychology' THEN 'Psychology'
            WHEN 'trad_cook' THEN 'Traditional Cooking'
            ELSE 'Not yet categorized'
        END
FROM titles
ORDER BY price
go

Price Category        Shortened Title      Category
--------------------- -------------------- -------------------
Not yet priced        Net Etiquette        Popular Computing
Not yet priced        The Psychology of Co Not yet categorized
Very Reasonable Title The Gourmet Microwav Modern Cooking
Very Reasonable Title You Can Combat Compu Business
Very Reasonable Title Life Without Fear    Psychology
Very Reasonable Title Emotional Security:  Psychology
Coffee Table Title    Is Anger the Enemy?  Psychology
Coffee Table Title    Cooking with Compute Business
Coffee Table Title    Fifty Years in Bucki Traditional Cooking
Coffee Table Title    Sushi, Anyone?       Traditional Cooking
Coffee Table Title    Prolonged Data Depri Psychology
Coffee Table Title    Silicon Valley Gastr Modern Cooking
Coffee Table Title    Straight Talk About  Business
Coffee Table Title    The Busy Executive's Business
Expensive book!       Secrets of Silicon V Popular Computing
Expensive book!       Onions, Leeks, and G Traditional Cooking
Expensive book!       Computer Phobic And  Psychology
Expensive book!       But Is It User Frien Popular Computing

(18 row(s) affected)

C. SELECT Statement with Searched CASE Expressions and Nested Subquery

Within a SELECT statement, the searched CASE expression allows values to be replaced in the results set based on comparison values. In this example, the royalty percentage is checked in a separate table to determine a royalty category for each author.

SELECT "Author's Full Name" = CONVERT(varchar(25), (RTRIM(au_fname) 
                             ' '  RTRIM(au_lname))),
    "Shortened Title" = CONVERT(varchar(25), title),
    "Royalty Category" =
        CASE
            WHEN (SELECT AVG(royaltyper) FROM titleauthor ta
                    WHERE t.title_id = ta.title_id) > 60
                THEN 'High Royalty'
            WHEN (SELECT AVG(royaltyper) FROM titleauthor ta
                    WHERE t.title_id = ta.title_id)
                        BETWEEN 41 and 59
                THEN 'Medium Royalty'
            ELSE 'Low Royalty'
        END
FROM authors a, titles t, titleauthor ta
WHERE a.au_id = ta.au_id AND ta.title_id = t.title_id
ORDER BY "Royalty Category", "Author's Full Name"
go
Author's Full Name        Shortened Title           Royalty Category
------------------------- ------------------------- ----------------
Akiko Yokomoto            Sushi, Anyone?            Low Royalty
Burt Gringlesby           Sushi, Anyone?            Low Royalty
Michael O'Leary           Sushi, Anyone?            Low Royalty
Albert Ringer             Life Without Fear         High Royalty
Charlene Locksley         Emotional Security: A New High Royalty
Charlene Locksley         Net Etiquette             High Royalty
Cheryl Carson             But Is It User Friendly?  High Royalty
Innes del Castillo        Silicon Valley Gastronomi High Royalty
Johnson White             Prolonged Data Deprivatio High Royalty
Marjorie Green            You Can Combat Computer S High Royalty
Reginald Blotchet-Halls   Fifty Years in Buckingham High Royalty
Sylvia Panteley           Onions, Leeks, and Garlic High Royalty
Abraham Bennet            The Busy Executive's Data Medium Royalty
Albert Ringer             Is Anger the Enemy?       Medium Royalty
Ann Dull                  Secrets of Silicon Valley Medium Royalty
Anne Ringer               Is Anger the Enemy?       Medium Royalty
Anne Ringer               The Gourmet Microwave     Medium Royalty
Livia Karsen              Computer Phobic And Non-P Medium Royalty
Marjorie Green            The Busy Executive's Data Medium Royalty
Michael O'Leary           Cooking with Computers: S Medium Royalty
Michel DeFrance           The Gourmet Microwave     Medium Royalty
Sheryl Hunter             Secrets of Silicon Valley Medium Royalty
Stearns MacFeather        Computer Phobic And Non-P Medium Royalty
Stearns MacFeather        Cooking with Computers: S Medium Royalty

(25 row(s) affected)

D. UPDATE Statement with CASE Expressions

With an UPDATE statement and CASE expressions, it's easy to modify a significant number of rows based on multiple columns of conditional information. In this example, reviews have been turned in and salary increases are due. A review rating of 4 will double the worker's salary, 3 will increase it by 60 percent, 2 will increase it by 20 percent, and a rating lower than 2 results in no raise. Also, a raise will not be given if the employee has been at the company for less than 18 months.

UPDATE employee_salaries
    SET salary =
        CASE 
            WHEN (review = 4 AND 
                (DATEDIFF(month, hire_date, GETDATE()) > 18)) 
                THEN salary * 2
            WHEN (review = 3 AND 
                (DATEDIFF(month, hire_date, GETDATE()) > 18)) 
                THEN salary * 1.6
            WHEN (review = 2 AND 
                (DATEDIFF(month, hire_date, GETDATE()) > 18)) 
                THEN salary * 1.2
            ELSE salary
        END

E. CASE-related Function (COALESCE)

In this example, a wages table is shown to include three columns with information about an employee's yearly wage: hourly_wage, salary, and commission. However, an employee receives only one type of pay. To determine the total amount paid to all employees, use the COALESCE function to receive only the non-null value found in hourly_wage, salary, and commission.

CREATE TABLE wages
(
    emp_id        tinyint     identity,
    hourly_wage    decimal    NULL,
    salary        decimal     NULL,
    commission    decimal    NULL,
    num_sales    tinyint    NULL
)
INSERT wages VALUES(10.00, NULL, NULL, NULL)
INSERT wages VALUES(20.00, NULL, NULL, NULL)
INSERT wages VALUES(30.00, NULL, NULL, NULL)
INSERT wages VALUES(40.00, NULL, NULL, NULL)
INSERT wages VALUES(NULL, 10000.00, NULL, NULL)
INSERT wages VALUES(NULL, 20000.00, NULL, NULL)
INSERT wages VALUES(NULL, 30000.00, NULL, NULL)
INSERT wages VALUES(NULL, 40000.00, NULL, NULL)
INSERT wages VALUES(NULL, NULL, 15000, 3)
INSERT wages VALUES(NULL, NULL, 25000, 2)
INSERT wages VALUES(NULL, NULL, 20000, 6)
INSERT wages VALUES(NULL, NULL, 14000, 4)
go

SELECT "Total Salary" = CONVERT(money, 
        (COALESCE(hourly_wage * 40 * 52, 
            salary, 
            commission * num_sales)))
    FROM wages
go

Total Salary               
-------------------------- 
20,800.00                  
41,600.00                  
62,400.00                  
83,200.00                  
10,000.00                  
20,000.00                  
30,000.00                  
40,000.00                  
45,000.00                  
50,000.00                  
120,000.00                 
56,000.00                  

(12 row(s) affected)

F. CASE-related Functions (NULLIF and COALESCE)

In this example, the budgets table is created to show a department (dept), its current budget (current_year) and its previous budget (previous_year). For the current year, NULL is used for departments whose budget will not change from the prior year, and 0 is used for budgets that have not yet been determined. To find out the average of only those departments that have received a budget as well as include the budget value from the prior year (use the previous_year value, where the current_year is 0), combine the NULLIF and COALESCE functions.

CREATE TABLE budgets
(
    dept                tinyint    IDENTITY,
    current_year        decimal    NULL,
    previous_year    decimal    NULL
)
INSERT budgets VALUES(100000, 150000)
INSERT budgets VALUES(NULL, 300000)
INSERT budgets VALUES(0, 100000)
INSERT budgets VALUES(NULL, 150000)
INSERT budgets VALUES(300000, 250000)

SELECT "Average Budget" = AVG(NULLIF(COALESCE(current_year,
                             previous_year), 0.00)) 
    FROM budgets
go

Average Budget                           
---------------------------------------- 
212500.000000                            

(1 row(s) affected)