See Also: Main_Page - Transact SQL Language Elements - Loops & Conditional Processing
CASE expressions are commonly used in programming languages for performing conditional processing when IF constructs get somewhat cumbersome and code begins to be nested several levels deep. Although CASE expressions are supported in T-SQL, they are quite a bit different from CASE usage in other languages.
There are a couple of different ways of using CASE expressions. The simple CASE expression compares a specific value to a list of other values and returns a different result in each circumstance. The general syntax for the simple CASE expression is as follows: CASE input_expression
WHEN when_expression THEN result_expression
[ ...n ]
[
ELSE else_result_expression
]
END
For example, the following SELECT statement returns different regions depending on the state where each author resides. Notice that if the CASE expression cannot find the values that it is searching for, then a catchall value, specified with ELSE, will be returned: SELECT au_lname, au_fname, region =
CASE WHEN state = 'ca' THEN 'west'
WHEN state = 'tn' THEN 'south'
WHEN state = 'mi' THEN 'north'
WHEN state = 'in' THEN 'north'
WHEN state = 'or' THEN 'west'
WHEN state = 'ut' THEN 'north-west'
WHEN state = 'md' THEN 'north-east'
ELSE 'central'
END
FROM authors
Results (abbreviated): au_lname au_fname region
------------ -------------- ----------
White Johnson west
Green Marjorie west
Carson Cheryl west
O'Leary Michael west
Straight Dean west
Smith Meander central
Bennet Abraham west
Dull Ann west
Gringlesby Burt west
Locksley Charlene west
Greene Morningstar south
del Castillo Innes north
DeFrance Michel north
Another way of using the CASE expression (sometimes referred to as searched case) is to perform some conditional checks and return the value accordingly. The syntax for the searched CASE is as follows: CASE
WHEN Boolean_expression THEN result_expression [ ...n ]
[ ELSE else_result_expression ]
END
The following example will determine the output depending on the quantity column in the sales table: SELECT title, category = CASE
WHEN SUM(qty) > 50 THEN 'best-seller'
WHEN SUM(qty) BETWEEN 25 AND 50 THEN 'good-seller'
WHEN SUM(qty) BETWEEN 10 AND 24 THEN 'average-seller'
ELSE 'poor-seller'
END
FROM titles a, sales b
WHERE
a.title_id *= b.title_id
GROUP BY title
Results: title category
-------------------------------------------------------------------- --------------
But Is It User Friendly? good-seller
Computer Phobic AND Non-Phobic Individuals: Behavior Variations average-seller
Cooking with Computers: Surreptitious Balance Sheets good-seller
Emotional Security: A New Algorithm good-seller
Fifty Years in Buckingham Palace Kitchens average-seller
Is Anger the Enemy? best-seller
Life Without Fear good-seller
Net Etiquette poor-seller
Onions, Leeks, and Garlic: Cooking Secrets of the Mediterranean good-seller
Prolonged Data Deprivation: Four Case Studies average-seller
Secrets of Silicon Valley good-seller
Silicon Valley Gastronomic Treats average-seller
Straight Talk About Computers average-seller
Sushi, Anyone? average-seller
The Busy Executive's Database Guide average-seller
The Gourmet Microwave good-seller
The Psychology of Computer Cooking poor-seller
You Can Combat Computer Stress! good-seller
Again, if the CASE expression cannot find the condition it will provide the catchall value (in this case "poor-seller") in the output.
Both simple and searched CASE have a very similar usage. The main difference is that the searched CASE will evaluate conditions, whereas the simple CASE will examine actual data values.
Note: It is important to note that CASE stops searching as soon as it finds a satisfactory value or condition, therefore, be sure to arrange the conditions properly in case several of the conditions might qualify.
CASE expressions can be used not only with SELECT, but also with UPDATE. For instance, suppose we wanted to market our titles according to how they have done in the past years. First, let's create a temporary table and populate it with values shown in the previous example: CREATE TABLE #title_strategy (title VARCHAR(80) NOT NULL,
category VARCHAR(55) NOT NULL, strategy VARCHAR(65) NULL)
INSERT #title_strategy (title, category)
SELECT title, category = CASE
WHEN SUM(qty) BETWEEN 10 AND 24 THEN 'average-seller'
WHEN SUM(qty) BETWEEN 25 AND 50 THEN 'good-seller'
WHEN SUM(qty) > 50 THEN 'best-seller'
ELSE 'poor-seller'
END
FROM titles a, sales b
WHERE
a.title_id *= b.title_id
GROUP BY title
The following query will update values in a temporary table depending on values in other columns:
UPDATE #title_strategy SET strategy = CASE
WHEN category = 'best-seller' THEN 'market to new and old stores'
WHEN category = 'good-seller'THEN 'market to new stores'
WHEN category = 'average-seller' THEN 'market to old stores'
ELSE 'discontinue'
END
You can also use CASE to generate the column headings - thereby creating a cross-tab query. The following example creates a sales report for each month of the year: SELECT DATEPART(yy, ord_date) 'year',
SUM(CASE DATEPART(MM, ord_date) WHEN 1 THEN qty else 0 END) 'January',
SUM(CASE DATEPART(MM, ord_date) WHEN 2 THEN qty else 0 END) 'February',
SUM(CASE DATEPART(MM, ord_date) WHEN 3 THEN qty else 0 END) 'March',
SUM(CASE DATEPART(MM, ord_date) WHEN 4 THEN qty else 0 END) 'April',
SUM(CASE DATEPART(MM, ord_date) WHEN 5 THEN qty else 0 END) 'May'
FROM sales
GROUP BY DATEPART(yy, ord_date)
Results: year January February March April May
----------- ----------- ----------- ----------- ----------- -----------
1992 0 0 0 0 0
1993 0 35 25 0 165
1994 0 0 0 0 0
The searched CASE can be used to examine any conditional statement, therefore, you could also use CASE with sub-queries. The following query will examine the job level of each employee, compare it to the minimum and maximum levels and provide appropriate rating: SELECT fname, lname, rating = CASE
WHEN job_lvl = (SELECT MAX(job_lvl) FROM employee) THEN 'well done!'
WHEN job_lvl > (SELECT AVG(job_lvl) FROM employee) THEN 'doing good'
WHEN job_lvl < (SELECT AVG(job_lvl) FROM employee) AND
job_lvl > (SELECT MIN(job_lvl) FROM employee)
THEN 'better hurry'
ELSE 'sorry'
END
FROM employee
Results (abbreviated): fname lname rating
-------------------- ------------------------------ ------------
Pedro Afonso better hurry
Victoria Ashworth doing good
Helen Bennett better hury
Lesley Brown better hurry
Francisco Chang doing good
Philip Cramer doing good
Aria Cruz better hurry
Ann Devon doing good
Anabela Domingues better hurry
Peter Franken better hurry
Paolo green better hurry
Paul Henriot doing good
Carlos Hernadez doing good
Palle Ibsen doing good
Karla Jablonski doing good
Karin Josephs better hurry
Yoshi Latimer sorry
Laurence Lebihan doing good
Elizabeth Lincoln better hurry
Patricia McKenna doing good
Roland Mendel doing good
Rita Muller doing good
Helvetius Nagy better hurry
Timothy O'Rourke better hurry
Sven Ottlieb doing good
Miguel Paolino better hurry
Paula Parente better hurry
Manuel Pereira better hurry
Maria Pontes well done!
Martine Rance better hurry
Remember, CASE will not keep searching if it finds a satisfactory criterion. In this query, had we specified the job levels greater than the average before the maximum job level in the CASE expression none of employees would receive the rating of "well done". Once the CASE statement finds that the job level is greater than the average (which will be true for the maximum level), it will not check to see if any other conditions are met.
|