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:

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:

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:

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:

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:

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:

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.

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:

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:

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:

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

发表于 @ 2007年04月11日　13:57:00 | 评论( loading... ) | 编辑| 举报| 收藏

旧一篇:执行自定义分页和删除的bug解决 | 新一篇: Playing a bit with GridView "Sort Grouping"