The Power of CASE

The CASE expression is the most powerful and perhaps the most underrated and underused SQL feature. CASE expressions let you run compact, fast procedures, and can dramatically reduce the number of code lines, making the code more readable and understandable for the programmer. In Compared with traditional methods of using loops and temp tables, CASE expressions can significantly reduce execution time, development time, and the risk of bugs creeping in. Finally, CASE expressions can help you more easily create crosstab reports.

Crosstab reports are difficult to produce because you need to transpose row values to columns. For example, let’s say you need to total all January sales for a product and enter the result as a "January sales" column next to the product. You can use the CASE expression almost anywhere in a SQL statement. Here, I’ll use it inside a SUM() function to demonstrate how to create a sales report for different products by month, as Table 1 shows. (This is the sales table in the pubs database.) Using traditional logic of loops and temp tables, this process is usually very unwieldy, requiring a lot of duplication, as Listing 1 shows. By using a CASE expression, you can produce this report with one concise statement, as Listing 2 shows. Both methods produce the same results.

Listing 1 (the non-CASE method) requires several pages of code, with extensive duplication. The CASE method processes the sales table only once, whereas the alternative processes it 12 times. The CASE method will execute more quickly, especially when you’re dealing with very large amounts of data.

Clearly, other options may exist, but none compare with the power of CASE.

Go forth and spread the word.