Coauthored by Mike Gunderloy
Developers who are new to Transact-SQL (T-SQL) sometimes discover one part of the language and use it doggedly, even when there are better alternatives. An example is using the classic IF...THEN statement for controlling program flow. This is usually the first, and sometimes the only, flow control statement that new T-SQL developers learn. Occasionally, this can lead developers into trouble and create more work than necessary. For instance, suppose you need to print a list of all products and display the text "Expensive product" next to those products that cost more than $30 per unit, while displaying the text "Inexpensive product" next to the rest.
Trying to solve this problem with nothing but IF...THEN in your arsenal can be difficult, to say the least. You might decide to open a cursor and walk through every row in the table one at a time, feeding each individual row into an IF...THEN statement to decide what to print. But hold on! There's a better way: the CASE...WHEN statement.
The T-SQL CASE...WHEN statement is very similar to a switch or case statement in other computer languages. The key that makes it especially useful is that you can use CASE...WHEN within a SELECT statement. Technically, CASE evaluates a list of conditions and returns one of multiple possible result expressions—so it's easy to see how someone new to the language might try to use a series of IF...THEN statements instead of looking for something a little more powerful.
CASE has two formats: a simple CASE and a searched CASE. The simple CASE sequentially compares an expression to a set of values or to an expression to determine the result. A searched CASE evaluates a set of Boolean expressions to determine the result. Both formats support an ELSE argument—another confusing similarity to IF...THEN.
The simple CASE takes the form:
WHEN condition THEN trueresult
Where evaluatedvalue is the expression or value that the statement then passes through the WHEN conditions. The WHEN condition component compares evaluatedvalue to condition, and, if the comparison is True, the statement executes the THEN trueresult component. The optional ...n component refers to a set of multiple conditions and results for those times when more than one condition and result exists. When none of the WHEN conditions return a True value, the optional ELSE elseresult kicks in and returns elseresult. When there's no ELSE value to fall back on, the function returns a Null value. The result values must be of the same data type or support an implicit conversion.
Listing A is an example of a simple CASE based on the sample Northwind database:
The results include a product description for each product, as shown in Figure A. The same thing could be accomplished with a join on the Products and Categories table, but it's a good example of a simple CASE.
|Use a simple CASE to list a number of conditions and possible results.|
A searched query takes the form
WHEN booleanexpression THEN trueresult
In this form, trueresult is executed when booleanexpression evaluates to True. As with the simple form, the ELSE statement is executed if none of the WHEN expressions result in a True value; if there is no ELSE value, the function returns a Null value.
Earlier, we mentioned the problem of printing a product list with a price disclaimer using the IF...THEN statement; that problem can be solved with the search CASE shown in Listing B.
The results are shown in Figure B. This example evaluates each row retrieved by the SELECT statement, which, in this case, includes all the records in the Products table. If the Unit Price is greater than $30, the CASE expression uses the string Expensive product as that row's Product Type value. When Unit Price is less than $30, the statement enters Inexpensive product as the Product Type value.
|The search CASE looks for a True Boolean value.|
The search CASE supports multiple WHEN statements, but it evaluates them one at a time until it finds one that evaluates to True. When it does, the corresponding THEN becomes the value of the CASE...WHEN. The ELSE value is used if none of the WHEN expressions evaluates to True. Be careful, because more than one booleanvalue expression could evaluate to True—make sure your logic is in order. The CASE statement stops evaluating alternatives as soon as any of the WHEN statements return True.
Make the Case
As great as the IF...THEN statement is, it isn't the best or only control flow statement. The CASE...WHEN statement can be much more flexible in the right circumstances, especially since you can use it within a SELECT statement. Don't get stuck in a rut; use all the T-SQL tools at your disposal.
Susan Sales Harkins is an IT consultant, specializing in desktop solutions. Previously, she was editor in chief for The Cobb Group, the world's largest publisher of technical journals.