To read the article online, visit http://www.4GuysFromRolla.com/webtech/102704-1.shtml

The Power of SQL CASE Statements

By Scott Mitchell

Introduction
As developers creating data-driven Web applications, we've all written more than our fair share of SQL statements. If I had a nickel for every SELECT statement or UPDATE statement I've written in my six years as a Web developer, I'd be a rich man. Despite the familiarity with SQL that many Web developers have, one of the lesser known SQL tools is the CASE statement. The CASE statement is a wily little fellow that can be added to both the SELECT portion of a SELECT statement as well as the ORDER BY portion. With CASE you can easily group data into various ranges, you can beautify the results of a SQL query, and can allow for dynamic sorting of the query's results. CASE statements can also be used to give subtitles to ROLLUP and CUBE queries, and can be used in computed columns to boot.

In this article we'll take a look at the powerful CASE statement, shining light on its utility and usefulness in everyday data-driven Web sites. Read on to learn more!

CASE Basics
All programming languages contain conditional statements to allow program flow to be altered based on the result of some conditional statement, the simplest form of a conditional statement typically being an If statement. One downside of If statements is that if there are a number of different actions that must be taken based on some variable value, the number of If statements can explode, leading to bloated, unreadable code. For example, imagine that you wanted to do some different action based on the hour of the day. If a different action was performed for each hour of the day, you would end up having 24 If/ElseIf statements like so: 

If Hour(Now()) = 0 Then
  'Do whatever needs to be done at midnight
ElseIf Hour(Now()) = 1 Then
  'Do whatever needs to be done at 1:00 am
ElseIf Hour(Now()) = 2 Then
  ...
...
End If

The multiple If statements detract from the readability of the code. Also, the maintainability of the code suffers. Imagine that you needed to change the code so that task x was run at both 3:00 am and 9:00 am. This would require picking your way through the If statements and adding additional conditional statements where needed.

Many programming languages provide case statements: Visual Basic offers a Select Case, C#/C/C++/Java the switch statement. These case statements allow for the myriad of If statements to be compressed into a more readable block of code. An example Select Case statement can be seen below: 

Select Case Hour(Now())
  Case 0
    'Do whatever needs to be done at midnight
  Case 1
    'Do whatever needs to be done at 1:00 am
  Case 2
    ...
  ...
End Select

Case Statements are More Performant
In addition to improved readability, case statements also provided an opportunity for optimization. For a more detailed discussion on case statement optimizations check out Switch Statement Performance Consideration for a more thorough discussion.

Not to be outdone, T-SQL contains a CASE statement as well. The T-SQL CASE statement has two flavors:

  1. A simple case statement, whose syntax and semantics closely mirror those case statements found in other programming languages. The generic form for the simple case statement is: 

    CASE expression
  WHEN value1 THEN result1
  WHEN value2 THEN result2
  ...
  WHEN valueN THEN resultN
  
  [
    ELSE elseResult
  ]
END

  2. A "searched CASE form" that instead of specifying a single expression, contains a series of Boolean expressions in each of the WHEN clauses: 

    CASE
  WHEN booleanExpression1 THEN result1
  WHEN booleanExpression2 THEN result2
  ...
  WHEN booleanExpressionN THEN resultN
  
  [
    ELSE elseResult
  ]
END

Now that we've looked at the generic syntax for T-SQL CASE statements, let's turn our attention to examining how CASE can be used in real-world applications.

Prettying Up Your Output
The simplest use of CASE is for tweaking the output. For example, imagine you have a table with employee information that contains a char(1) field called Gender, with a value of M for males and F for females. You might want to have the output be Male and Female instead of M and F. This can easily be accomplished with CASE as the following example illustrates: 

SELECT
  FirstName, LastName,
  Salary, DOB,
  CASE Gender
    WHEN 'M' THEN 'Male'
    WHEN 'F' THEN 'Female'
  END
FROM Employees

For more examples of enhancing the output with CASE be sure to read The Case for CASE.

Formatting Summation Columns
The SQL syntax offers a number of aggregate functions, such as AVG(), SUM(), MAX(), and so forth, allowing for averages, sums, maxes, minimums, and so on to be grouped by particular field values. Returning to the employees example, if each employee worked for a department specified by a Department field, and if each employee had a Salary field, you could display the average salary and total salary per department using a query like: 

SELECT
  Department, AVG(Salary) as AvgSalary
FROM Employees
GROUP BY Department

The output of this query might look like:


Department       AvgSalary
Sales            78,500.00
Marketing        81,250.00
IT               55,000.50
Executive        91,900.75
You might also want to know the average salary for the entire company, across all departments. SQL provides the ROLLUP keyword to summarize aggregated data. For example, adjusting the query by adding WITH ROLLUP, as in: 

SELECT
  Department, AVG(Salary) as AvgSalary
FROM Employees
GROUP BY Department
WITH ROLLUP

Might generate the following output:


Department       AvgSalary
Sales            78,500.00
Marketing        81,250.00
IT               55,000.50
Executive        91,900.75
NULL             76,662.81
The problem here is that the last row that provides the summary has a NULL value for the Department field. Ideally, this would have a value like Company Average. Well, SQL provides a GROUPING(columnName) function that returns a 1 when the specified column name is for a summarized row. Using this inside a CASE statement, you could achieve the desired output like so: 

SELECT
  CASE 
    WHEN GROUPING(Department) = 1 THEN 'Company Average'
    ELSE Department
  END AS Department,
  AVG(Salary) as AvgSalary
FROM Employees
GROUP BY Department
WITH ROLLUP

For more on this technique, including how to display summarized data within a DataGrid, be sure to read Dave Long's article Summarizing Data with ROLLUP.

Used to Dynamically Sort Query Results
Ideally when creating data-driven Web applications, all SQL statements are encapsulated within stored procedures. When using an ASP.NET DataGrid, you can easily bind the results of a stored procedure to the DataGrid. However, if you want to implement sorting within the DataGrid, you need some means to dynamically order the stored procedure's results. There are a couple of techniques to accomplish this. A common one is to read the stored procedure results into a DataTable, and then use the DataTable's Sort property to sort the results by the specified column. If you want to stay away from DataTables and use just the more efficient DataReaders, you might pine for some means to pass in the column to sort by into the stored procedure. Unfortunately, the following stored procedure is not valid. 

CREATE PROCEDURE getEmployees ( @OrderByClause varchar(100) ) AS

SELECT EmployeeID, FirstName, LastName, SSN, Salary
FROM Employees
ORDER BY @OrderByClause

What you can do, however, is use a CASE statement in the ORDER BY clause to specify what column to sort the results by based upon the passed in @OrderByClause parameter. The syntax looks like: 

CREATE PROCEDURE getEmployees ( @ColumnName varchar(100) ) AS

SELECT EmployeeID, FirstName, LastName, SSN, Salary
FROM Employees
ORDER BY
  CASE WHEN @ColumnName='LastName' THEN LastName
       WHEN @ColumnName='Salary' THEN CONVERT(varchar(50), Salary)
       WHEN @ColumnName='SSN' THEN SSN
  END

Note that the input parameter is a varchar(100) and can specify one of the column names to sort by. The CASE statement in the ORDER BY clause determines what column to sort by based upon the value passed in.

There are a couple caveats when using this approach for dynamic ordering. First, dynamic ordering doesn't work for mixed data types. That is, if you have fields of differing data types that you want to order by - such as varchars and decimals, as is the case with LastName and Salary above - then in the CASE statement you need to use CONVERT to convert the mismatched data type to the same data type as the others. Note how in the second WHEN line in the example above, Salary is converted to a varchar(50). You'll have to do this with datetime, int and other non-character data types as well.

For a more in-depth look at this technique for dynamically sorting the results of stored procedures, be sure to read: Dynamic ORDER BY Statements in Stored Procedures.

Grouping Results Into Ranges
The CASE statement can also be used to group data into ranges. I recently helped a client create a system for sending invoices, and he needed a report that showed the total amount of money a customer was invoiced in the past 30 days, in the past 30-60 days, in the past 60-90 days, and 90+ days past. This was easily accomplished with a series of CASE statements within SUM() functions like so: 

SELECT
  SUM(CASE 
         WHEN DateDiff(d, InvoiceDate, getdate()) BETWEEN 0 AND 29 THEN InvoiceAmount 
      END) as Current,
  SUM(CASE 
         WHEN DateDiff(d, InvoiceDate, getdate()) BETWEEN 30 AND 59 THEN InvoiceAmount 
      END) as ThirtyToSixty,
  SUM(CASE 
         WHEN DateDiff(d, InvoiceDate, getdate()) BETWEEN 60 AND 89 THEN InvoiceAmount 
      END) as SixtyToNinty,
  SUM(CASE 
         WHEN DateDiff(d, InvoiceDate, getdate()) >= 90 THEN InvoiceAmount 
      END) as NintyPlus
FROM Invoices

Using CASE in Computed Columns
With SQL Server you can create computed columns that are comprised of data from other columns in the table. For example, in a table storing medical information for patients, there might be Weight and Height fields, with a computed column for the BMI. (BMI, or Body Mass Index, is a ratio of a person's height and weight, and is a figure that is commonly used to ascertain if a patient is underweight, normal weight, overweight, or obese.)

When creating a computed column you can use a CASE statement in the computation. For example, imagine that you had a table with information about classes offered by a local college. This table might have two int fields that recorded the number of students and professors participating in the class (call these fields Students and Professors). Now, you might want to add a computed field that returned the ratio of students to professors, providing the formula Students / Professors. The problem here is that if there is a class with no professors, the computed column will barf since division by 0 is not allowed. To surmount this problem, you could use a computed column formula with a CASE statement that returned NULL (or some predefined value, such as 0) if Professors equaled 0. That is, you'd have your formula become: 

CASE
  WHEN Faculty = 0 THEN NULL
  ELSE Students / Faculty
END

For more information on this technique, check out this blog entry: Computed Columns and Divide By Zero Errors.

Conclusion
In this article we looked at SQL's CASE statement, a useful little bugger that, sadly, doesn't get the press he deserves. CASE statements can be used to format the output of your SQL queries, to group data into various ranges, to allow for dynamic ordering of SQL results in a stored procedure, used as conditionals in computed columns, and used to format summation columns.

Happy Programming!

  • By Scott Mitchell

    • Article Information
    Article Title: The Power of SQL CASE Statements
    Article Author: Scott Mitchell
    Published Date: Wednesday, October 27, 2004
    Article URL: http://www.4GuysFromRolla.com/webtech/102704-1.shtml

    Copyright 2013 QuinStreet Inc. All Rights Reserved.
    Legal Notices, Licensing, Permissions, Privacy Policy.
    Advertise | Newsletters | E-mail Offers