SQL CASE

CASE expressions bring a vast degree of power and control to SQL Server programmers. A working knowledge of CASE expressions can make accessing and updating SQL Server data easier and perhaps, even more efficient. Additionally, CASE expressions enable more work to be accomplished using a single SQL statement, which should also improve efficiency and decrease development time. As such, CASE expressions should be a component of every SQL Server developer’s arsenal of programming techniques.

select title, price, Budget = CASE
WHEN price > 20.00 THEN 'Expensive'
WHEN price BETWEEN 10.00 AND 19.99 THEN 'Moderate'
WHEN price < 10.00 THEN 'Inexpensive'
ELSE 'Unknown'

END
FROM title

Result
title price budget
-------------------------------------------------------------------------------- --------- -----------
The Busy Executive's Database Guide 19.99 Moderate
Cooking with Computers: Surreptitious Balance Sheets 11.95 Moderate
You Can Combat Computer Stress! 2.99 Inexpensive
Straight Talk About Computers 19.99 Moderate
Silicon Valley Gastronomic Treats 19.99 Moderate
The Gourmet Microwave 2.99 Inexpensive
The Psychology of Computer Cooking NULL Unknown
But Is It User Friendly? 22.95 Expensive
Secrets of Silicon Valley 20.00 Unknown
Net Etiquette NULL Unknown
Computer Phobic AND Non-Phobic Individuals: Behavior Variations 21.59 Expensive
Is Anger the Enemy? 10.95 Moderate
Life Without Fear 7.00 Inexpensive
Prolonged Data Deprivation: Four Case Studies 19.99 Moderate
Emotional Security: A New Algorithm 7.99 Inexpensive
Onions, Leeks, and Garlic: Cooking Secrets of the Mediterranean 20.95 Expensive
Fifty Years in Buckingham Palace Kitchens 11.95 Moderate
Sushi, Anyone? 14.99 Moderate

SQL Statement #2

select top(10) au_lname, au_fname, isSocalMan=
case state when 'CA' then 'Yes, he is.'
else 'No, he is not' end
from authors

au_lname au_fname isSocalMan
---------------------------------------- -------------------- -------------
White Johnson Yes, he is.
Green Marjorie Yes, he is.
Carson Cheryl Yes, he is.
O'Leary Michael Yes, he is.
Straight Dean Yes, he is.
Smith Meander No, he is not
Bennet Abraham Yes, he is.
Dull Ann Yes, he is.
Gringlesby Burt Yes, he is.
Locksley Charlene Yes, he is.
(10 row(s) affected)

SQL Statement #3

use northwind
go
SELECT
EmployeeID,
SUM(CASE DATEPART(mm,ShippedDate) WHEN 1 THEN Freight ELSE 0 END) AS jan,
SUM(CASE DATEPART(mm,ShippedDate) WHEN 2 THEN Freight ELSE 0 END) AS feb,
SUM(CASE DATEPART(mm,ShippedDate) WHEN 3 THEN Freight ELSE 0 END) AS mar,
SUM(CASE DATEPART(mm,ShippedDate) WHEN 4 THEN Freight ELSE 0 END) AS apr,
SUM(CASE DATEPART(mm,ShippedDate) WHEN 5 THEN Freight ELSE 0 END) AS may,
SUM(CASE DATEPART(mm,ShippedDate) WHEN 6 THEN Freight ELSE 0 END) AS jun,
SUM(CASE DATEPART(mm,ShippedDate) WHEN 7 THEN Freight ELSE 0 END) AS jul,
SUM(CASE DATEPART(mm,ShippedDate) WHEN 8 THEN Freight ELSE 0 END) AS aug,
SUM(CASE DATEPART(mm,ShippedDate) WHEN 9 THEN Freight ELSE 0 END) AS sep,
SUM(CASE DATEPART(mm,ShippedDate) WHEN 10 THEN Freight ELSE 0 END) AS oct,
SUM(CASE DATEPART(mm,ShippedDate) WHEN 11 THEN Freight ELSE 0 END) AS nov,
SUM(CASE DATEPART(mm,ShippedDate) WHEN 12 THEN Freight ELSE 0 END) AS dec
FROM orders
GROUP BY EmployeeID
go

EmployeeID jan feb mar apr may jun jul aug sep oct nov dec
------------------------------------------------------------------------------ --------------------- --------------------- --------------------- ---------------------
1 969.88 371.06 856.37 1092.84 599.10 129.89 703.69 1226.63 352.01 720.55 704.70 1035.46
2 907.20 400.65 894.20 2368.82 376.87 545.15 298.73 182.74 430.22 1129.68 278.44 636.96
3 924.92 1644.45 1237.53 772.87 1290.63 1570.36 213.81 295.83 381.62 556.12 773.17 1223.43
4 703.92 2279.89 811.89 960.03 371.34 245.27 594.36 1332.21 818.09 1336.58 371.08 1161.78
5 708.31 621.09 201.94 8.80 237.93 127.34 314.58 10.76 283.91 160.98 328.19 914.88
6 328.57 75.03 372.72 785.08 107.47 260.54 41.59 394.19 261.35 385. 30 321.27 373.61
7 1035.48 436.37 697.20 1416.70 427.74 89.16 108.68 713.51 585.20 528. 22 492.69 33.80
8 838.61 435.37 1278.18 1221.37 619.74 55.72 81.69 828.82 190.84 1018. 74 388.16 429.46
9 234.20 615.34 58.09 945.26 32.33 210.53 294.87 0.00 238.08 520.89 130.44 15.09

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.
Syntax
CASE input_expression
WHEN when_expression THEN result_expression
[ ...n ]
[
ELSE else_result_expression
]
END

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

Arguments
input_expression
The expression evaluated when you use the simple CASE format. The input_expression argument is any valid expression in Microsoft SQL Server 2005 Mobile Edition (SQL Server Mobile).

WHEN when_expression
A simple expression to which input_expression is compared when you use the simple CASE format. The when_expression argument is any valid SQL Server expression. The data types of input_expression and each when_expression must be the same or must be implicitly converted.

n
A placeholder indicating that multiple WHEN when_expression THEN result_expression clauses, or multiple WHEN Boolean_expression THEN result_expression clauses can be used.

THEN result_expression
The expression returned when input_expression equals when_expression evaluates to TRUE, or Boolean_expression evaluates to TRUE. The result expression argument is any valid SQL Server expression.

ELSE else_result_expression
The expression returned if no comparison operation evaluates to TRUE. If this argument is omitted and no comparison operation evaluates to TRUE, CASE returns NULL. The else_result_expression argument is any valid SQL Server expression. The data types of else_result_expression and any result_expression must be the same or must be an implicit conversion.

WHEN Boolean_expression
The Boolean expression evaluated when you use the searched CASE format. Boolean_expression is any valid Boolean expression.

Result Types
Returns the highest precedence type from the set of types in result_expressions and the optional else_result_expression.

Return Value
Simple CASE function:

• Evaluates input_expression, and then, in the order specified, evaluates input_expression = when_expression for each WHEN clause.
• Returns the result_expression of the first (input_expression = when_expression) that evaluates to TRUE.
• If no input_expression = when_expression evaluates to TRUE, else_result_expression is returned if an ELSE clause is specified, or NULL if no ELSE clause is specified.
  

Searched CASE function:

• Evaluates, in the order specified, Boolean_expression for each WHEN clause.
• Returns result_expression of the first Boolean_expression that evaluates to TRUE.
• If no Boolean_expression evaluates to TRUE, else_result_expression is returned if an ELSE clause is specified, or NULL if no ELSE clause is specified.
  

Example
The following example returns the mode of shipping used for orders placed.
SELECT ShipVia, CASE ShipVia
WHEN 1 THEN 'A.Datum'
WHEN 2 THEN 'Contoso'
WHEN 3 THEN 'Consolidated Messenger'
ELSE 'Unknown'
END
FROM Orders

Using CASE Expressions When Modifying Data

UPDATE titles SET price = CASE
WHEN (price < 5.0 AND ytd_sales > 999.99) THEN price * 1.25
WHEN (price < 5.0 AND ytd_sales < 1000.00) THEN price * 1.15
WHEN (price > 4.99 AND ytd_sales > 999.99) THEN price * 1.2
ELSE price

END

The bottom line is that CASE expressions are very powerful, yet often neglected.
Favor using CASE expressions under the following circumstances:

• when data needs to be converted from one type to another for display and no
  function exists to accomplish the task

• when a summary row needs to be created from detail data

• when conditional processing needs to be executed to determine results

• when using UNION to “glue together” different subsets of a single table
  
  
  

One final usage guideline is to use the COALESCE function with your CASE expressions when you wish to avoid NULLs. Consider an employee table that contains three columns for wages: salary, commission, and retainer_fee. Any single employee can only receive one of these types of wages. So, two of the columns will contain NULLs, but one will always contain a value. The following statement uses the COALESCE function to return only the non-NULL value:

SELECT
COALESCE(salary, commission, retainer_fee, 0)

FROM
employee

The COALESCE function will go through the list of expressions (in this case columns and constants) and return the first non-NULL value encountered. In this case, the numeric constant 0 is added at the end of the list to provide a default value if all of the columns should
happen to be NULL.