T-SQL 101: The CASE Function

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

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.

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.