T-SQL: CASE Statement
For the month of December, I’ll be taking a little holiday from blogging. In the meantime, enjoy a few of my more popular golden oldies, like this one (original post here: http://wp.me/pZM1Z-oB). And be sure to drop by our live weekly webshow, DBAs@Midnight, at 11pm Central time on Fridays!
This is a companion piece to the MidnightDBA video T-SQL: CASE Statement.
In short, a CASE statement is a simplified set of IF statements. Instead of using several IF statements, you can have a single statement that evaluates several criteria against a piece of data.
Simple CASE
Let’s take a simple example. We want to return the word “one” if @x = 1. First let’s do this with IF statements:
DECLARE @x tinyint = 1
IF @x = 2 SELECT 'Two'
ELSE IF @x = 1 SELECT 'One'
ELSE IF @x = 0 SELECT 'Zero'
ELSE SELECT 'Other'
And now the same thing, with a CASE statement:
DECLARE @x tinyint = 1
SELECT CASE @x
WHEN 2 THEN 'Two'
WHEN 1 THEN 'One'
WHEN 0 THEN 'Zero'
END AS number
This doesn’t look like that big of a deal. With IF, we have to write a few more characters, that’s all. But this is our simple example. Now let’s do the same thing with the ContactIDs of a few rows in AdventureWorks:
SELECT ContactID
, 'Two' AS number
FROM Person.Contact WHERE ContactID < 10 AND ContactID = 2
UNION
SELECT ContactID
, 'One' AS number
FROM Person.Contact WHERE ContactID < 10 AND ContactID = 1
UNION
SELECT ContactID
, 'Zero' AS number
FROM Person.Contact WHERE ContactID < 10 AND ContactID = 0
UNION
SELECT ContactID
, 'Other' AS number
FROM Person.Contact WHERE ContactID < 10 AND ContactID NOT IN (0, 1, 2)
And now using CASE:
SELECT ContactID
, CASE ContactID
WHEN 2 THEN 'Two'
WHEN 1 THEN 'One'
WHEN 0 THEN 'Zero'
ELSE 'Other'
END AS number
FROM Person.Contact WHERE ContactID < 10
The CASE statement is more compact and easier to use, yes. But it also performs better here: we’re making one call to the Contact table with CASE, as opposed to the four calls we had to make with IF.
Note that this last example has an ELSE statement after all the WHENs. ELSE is not required, but you wind up using it often. Use ELSE for the case where all of your WHEN statements evaluate false.
The simple CASE demonstrated above has limitations: You can only compare a single parameter – in this case, ContactID – for equality against a number of values. This means that all of these WHEN clauses are invalid:
SELECT MiddleName
, CASE MiddleName
WHEN NULL THEN 'Unknown' -- Ineffectual. NULL can't be = NULL.
WHEN LIKE 'R%' THEN 'R-something' -- Syntax error. You can't use = LIKE [value].
WHEN < 'B' THEN '"A" name' -- Syntax error. You can't use = < [value].
END AS Middle
FROM Person.Contact WHERE ContactID < 10
Searched Case
For greater flexibility, we have the searched case. Here’s an easy example:
DECLARE @x tinyint = 1
, @y varchar(10) = 'Howdy!'
, @z bit
SELECT CASE
WHEN @x > 2 THEN 'x is greater than 2. This is false.'
WHEN LEN(@y) = 10 THEN 'y is 10 characters long. This is false.'
WHEN @z IS NULL AND @y LIKE 'H%' THEN 'Z is null! Y starts with the letter "H"! This is true!'
ELSE 'Apparently we don''t know what''s happening.'
END AS statement
Each WHEN statement is independent; it can make whatever evaluations we want (e.g., =, <, >, IS NULL, IS NOT NULL, LIKE, and so on) on any available values. The first true WHEN statement is the one that returns a value; all others are ignored. The example above returns “Z is null! Y starts with the letter “H”! This is true!”
So let’s say we’re dividing up the customer list between a few employees…we want to contact everybody about a big new promotion. We’re prefer to email those with non-U.S. phone numbers (if they have an email listed), and divide the rest up between two interns:
SELECT LastName + ', ' + FirstName ContactName
, Phone
, EmailAddress
, CASE
WHEN Phone LIKE '1 (11)%' AND EmailAddress IS NOT NULL THEN 'Email'
WHEN Phone LIKE '1 (11)%' THEN 'Foreign Call Group'
WHEN Phone < '500' THEN 'Call Group 1'
WHEN Phone >= '500' THEN 'Call Group 2'
END AS Call_Group
FROM Person.Contact
ORDER BY Call_Group, ContactName
The first WHEN statement checks for international phone number and non-NULL email address. If this statement is false, SQL evaluates the next WHEN statement, and so on. In this way, each row is given the correct grouping value, and our resultset looks something like this:
| ContactName | Phone | EmailAddress | Call_Group |
| Abercrombie, Kim | 334-555-0137 | kim2@adventure-works.com | Call Group 1 |
| Achong, Gustavo | 398-555-0132 | gustavo0@adventure-works.com | Call Group 1 |
| Adams, Carla | 107-555-0138 | carla0@adventure-works.com | Call Group 1 |
| Adams, Jay | 158-555-0142 | jay1@adventure-works.com | Call Group 1 |
| Abel, Catherine | 747-555-0171 | catherine0@adventure-works.com | Call Group 2 |
| Acevedo, Humberto | 599-555-0127 | humberto0@adventure-works.com | Call Group 2 |
| Adams, Frances | 991-555-0183 | frances0@adventure-works.com | Call Group 2 |
| Agcaoili, Samuel | 554-555-0110 | samuel0@adventure-works.com | Call Group 2 |
| Ahlering, Robert | 678-555-0175 | robert1@adventure-works.com | Call Group 2 |
| Alberts, Amy | 727-555-0115 | amy1@adventure-works.com | Call Group 2 |
| Ferrier, François | 571-555-0128 | françois1@adventure-works.com | Call Group 2 |
| Smith, Margaret | 959-555-0151 | margaret0@adventure-works.com | Call Group 2 |
| Ackerman, Pilar | 1 (11) 500 555-0132 | pilar1@adventure-works.com | |
| Aguilar, James | 1 (11) 500 555-0198 | james2@adventure-works.com |
Note that – much like a subquery – CASE is not limited to the column list of the SELECT statement. You can use a CASE statement in the WHERE and JOIN…ON clauses. Generally speaking, though, it’s much easier, readable, and more performant to stick to AND/OR syntax in those cases.
Mini Cheat Sheet (straight outta BOL)
Simple CASE function:
CASE input_expression
WHEN when_expression THEN result_expression
[ ...n ]
[
ELSE else_result_expression
]
END
Searched CASE function:
CASE
WHEN Boolean_expression THEN result_expression
[ ...n ]
[
ELSE else_result_expression
]
END
|
Happy days,
Jen McCown
http://www.MidnightDBA.com/Jen
Recent Comments