Home » Beginner »sql server »sqlserverpedia-syndication »SSC »TSQL » Currently Reading:

T-SQL: CASE Statement

December 28, 2010 Beginner, sql server, sqlserverpedia-syndication, SSC, TSQL 4 Comments

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
  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:

  , 'Two' AS number
FROM Person.Contact WHERE ContactID < 10 AND ContactID = 2
  , 'One' AS number
FROM Person.Contact WHERE ContactID < 10 AND ContactID = 1
  , 'Zero' AS number
FROM Person.Contact WHERE ContactID < 10 AND ContactID = 0
  , 'Other' AS number
FROM Person.Contact WHERE ContactID < 10 AND ContactID NOT IN (0, 1, 2)

And now using CASE:

  , 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

    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 Email
Aguilar, James 1 (11) 500 555-0198 james2@adventure-works.com Email

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 
Searched CASE function:
     WHEN Boolean_expression THEN result_expression 
    [ ...n ] 
    ELSE else_result_expression 


Happy days,
Jen McCown

Currently there are "4 comments" on this Article:

  1. [...] This post was mentioned on Twitter by Jen & Sean McCown and Charles Proctor. Charles Proctor said: RT @MidnightDBA: T-SQL: CASE Statement http://tinyurl.com/23orkya In short, a CASE statement is a simplified set of IF statements <Nice [...]

  2. Dean B says:

    Good article but wrongly named – this is about the CASE expression. There is no CASE statement in Transact-SQL.

Comment on this Article:

Release Date: Minion by MidnightDBA

Just 2 days 17 hours 6 minutes 17 seconds until we release Minion Redindex: index maintenance done right!

MidnightSQL Consulting

Need help? Got an emergency? Write us at Support@MidnightDBA.com!

We can schedule time to help with your backup/restore issues, high availability and disaster recovery setup, performance problems, and a great deal more. Very often, we're even available on the moment for downtime issues and emergencies.

For more information about MidnightSQL consulting, email us or check out www.MidnightSQL.com. Happy days!

Where are We?

November 3-7: PASS Summit, Seattle, WA

PASS Summit: Jen is presenting How to Interview a DBA: A Panel Debate on Thursday 11/6 1:30pm, room 401 (along with Adam Machanic, Sean McCown, Bob Pusateri, and Michelle Ufford).

PASS Summit: Sean is presenting Performance Tuning Your Backups on Wednesday 11/5 3:00pm, room 602-604.

December 11: Presenting "Powershell Cmdlets.." at Alaska SQL Server User Group

January 30: "Become an Enterprise DBA" precon at Austin SQL Saturday

Blog Posts by Category


How to Eat Pop-tarts
Watch DBAs@Midnight live on Fridays,m 11pm Central time

The best database career advice you’ve never heard!


The DBA Roadmap Seminar is 7 MP3 tracks (over 5 hours!) of insider guidance on your database career. We'll teach you how and what to study as a DBA, weigh in on controversial resume debates, teach you to recognize a worthy recruiter, and discuss the new professionalism of interviews. Also some bonus materials, PDF companion guides, and really spiffy intro music!

Once your $99 PayPal payment is submitted, you'll get the download link in e-mail! (Download is a 370Mb ZIP file.)

Become a DBA. Become a BETTER DBA. Use the Roadmap.

Visit www.DBARoadmap.com for info, forums, and more!

Add to Cart View Cart

Cunningham’s Law

"The best way to get the right answer on the Internet is not to ask a question, it's to post the wrong answer."
Relevant: http://xkcd.com/386/