T-SQL 101: The CASE Function

Data is often stored in a format that's optimized for speed and efficiency but not necessarily understandability. Making sense of long lists of numerical codes might be second nature for some, but most people prefer to have more meaningful information displayed. Using T-SQL's CASE function, you can quickly transform data from one format to another. Before I tell you about the two types of CASE functions and give examples of how to use them, you need to make sure you have the right tables to run the sample code.

The Prerequisites

To run the sample code in this lesson, make sure your MyDB database contains the following tables:

If you haven't created these database objects, you'll find the code in the 100152.zip file. To download this file, click the 1"Download the Code"  hotlink at the top of the page.

The Two Types of CASE Functions

The CASE function is a powerful tool for evaluating several conditions and returning a single value for the first condition met. This function comes in two types: simple and searched. The simple CASE function examines an expression and compares it to a list of expressions. If a match is found, a specified result is returned. The searched CASE function examines a set of Boolean expressions. If a Boolean expression evaluates to true, a specified result is returned.

The simple CASE function consists of the following components:

The searched CASE function consists of the following components:

When you're writing a CASE function, you need to pay special attention to the ordering of the WHEN clauses. CASE functions evaluate WHEN clauses in sequential order and terminate on the first match found.

An Example of a Simple CASE Function

To see a simple CASE function in action, execute the code in Listing 1 in the MyDB database.

Listing 1: Query That Uses a Simple CASE Function

This query uses the Movie and MovieReview tables to produce a list of short reviews for the movies seen by a particular employee. The query's CASE function adds a short review based on the value in the Movie table's Stars column.

Note the use of the RIGHT OUTER JOIN clause in callout A in Listing 1. As I discussed in "T-SQL 101, Lesson 5," this type of join causes all records

in the Movie table to be included in the result set, even if there are no corresponding records in the MovieReview table. Non-corresponding MovieReview records are represented by NULL values. Because this code is filtering the records to only show reviews conducted by the employee whose ID is 1, you have to include the code OR EmployeeID IS NULL in the WHERE clause. Otherwise, these non-corresponding MovieReview records would be removed from the result set. The CASE function's ELSE clause transforms each NULL in the Stars column to the string Not yet rated in the Short Review column. Figure 1 shows the results.

Figure 1: Short movie reviews

An Example of a Searched CASE Function

To see a searched CASE function in action, execute the code in Listing 2 in the MyDB database.

Listing 2: Query That Uses a Searched CASE Function

This query produces a "career advice" report based on the salary information stored in the Employee table. The CASE function examines the salary of each employee and produces a custom message depending on the range the salary falls within. I wrapped the salary ranges in parentheses for readability and to indicate that each range statement is a Boolean expression. Figure 2 shows the results.

Figure 2: Career advice report

In Figure 2, note that Garret Testerson's salary is $100,000, which matches the upper range specified in the BETWEEN 75000 AND 100000 portion of the WHEN clause highlighted in callout A in Listing 2. Because the BETWEEN clause is inclusive (i.e., both the upper and lower values specified are included in the range), this Boolean expression evaluates to true. Garret's salary also meets the condition in the last WHEN clause, but by this point, the CASE function has already found a match and has terminated.

The Case for Using CASE

Simple and searched CASE functions let you easily transform and categorize data stored in databases. As the code in Listing 1 and Listing 2 show, you can use CASE functions in SELECT statements. However, you aren't limited to SELECT queries. You can use a CASE function to transform data in an UPDATE statement, in an INSERT statement, or wherever a valid SQL Server expression is required. So, the next time you're faced with having to transform data, try using a CASE function. You'll be glad you did.