Using the CASE expression instead of dynamic SQL in SQL Server
By: Armando Prato | Updated: 2008-03-14 | Comments (28) | Related: 1 | 2 | 3 | 4 | More > Dynamic SQL
Problem
I have a lot of SQL update queries where I need to make IF/ELSE types of decisions. I am using a cursor in some cases but looping through thousands of rows for update takes a long time. I also use some dynamic SQL to handle some query parameter decision making. Is there a better alternative?
Solution
The CASE expression is a really powerful tool that can you use to solve your SQL Server query problems. You're probably familiar with its use in mimicking if/else processing when issuing SELECT statements. However, its use is not confined strictly to this kind of processing.
Among the ways I've leveraged the CASE expression in my code:
- To eliminate a cursor loop when updating rows
- To perform specialized processing when using aggregate functions
- To create dynamic ORDER BY and WHERE clauses without using dynamic SQL
Let's look at some examples
We'll first create a new table called Customer and insert some rows
CREATE TABLE dbo.Customer |
Example 1
A requirement has come in to denormalize the table for reporting purposes by adding a state description column. Now, you could use a cursor and loop through the table, updating each row, but cursors can be performance killers. You could also create multiple UPDATE statements, but that would be unwieldly. Instead, you can use an UPDATE statement with CASE to efficiently update the table with one SET operation.
ALTER TABLE dbo.Customer ADD statedescription VARCHAR(50) NULL |
Example 2
A second requirement has come in where we need to report on the total number of all customers, the total number of all Massachusetts customers, and an average of all sales made by all Massachusetts customers. We could limit the query to just Massachusetts customers but that would make it cumbersome to get our count of total customers. To solve this problem, you can write the query to use a CASE expression within the aggregate functions to get Massachusetts specific information:
SELECT COUNT(*) AS TotalCustomers, |
Since NULL values are discarded when performing aggregate functions, we can easily get the required totals.
Example 3
Another requirement has come across our desk. We need a stored procedure that can be called by an application but the user wants to be able sort by either first name or last name. One would be tempted to use dynamic SQL to solve this problem, but we can use CASE to create a dynamic SQL equivalent
CREATE PROCEDURE dbo.getCustomerData @sortby VARCHAR(9), @sortdirection CHAR(4) |
Example 4
A final requirement has crossed our desk. We need to modify the stored procedure to search customers by a specific state. If the state is omitted, we should return customers for all states.
ALTER PROCEDURE dbo.getCustomerData @sortby VARCHAR(9), @sortdirection CHAR(4), @statecode VARCHAR(2) = NULL
|
Next Steps
- Read more about the CASE expression in the SQL Server 2000 and 2005 Books Online
- Examine your database update code for cursor loops that could make use of an UPDATE...CASE process
- Evaluate your complicated dynamic SQL logic to see if you can make use of CASE expressions
Last Updated: 2008-03-14
About the author

View all my tips