Using the CASE expression instead of dynamic SQL in SQL Server

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 ( customerid INT IDENTITY PRIMARY KEY, firstname VARCHAR(40) NOT NULL, lastname VARCHAR(40) NOT NULL, statecode VARCHAR(2) NOT NULL, totalsales money NOT NULL DEFAULT 0.00 ) INSERT INTO dbo.Customer (firstname, lastname, statecode, totalsales) SELECT 'Thomas', 'Jefferson', 'VA', 100.00 INSERT INTO dbo.Customer (firstname, lastname, statecode, totalsales) SELECT 'John', 'Adams', 'MA', 200.00 INSERT INTO dbo.Customer (firstname, lastname, statecode, totalsales) SELECT 'Paul', 'Revere', 'MA', 300.00 INSERT INTO dbo.Customer (firstname, lastname, statecode, totalsales) SELECT 'Ben', 'Franklin', 'PA', 400.00 GO

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 GO UPDATE dbo.Customer SET stateDescription = CASE WHEN statecode = 'MA' THEN 'Massachusetts' WHEN statecode = 'VA' THEN 'Virginia' WHEN statecode = 'PA' THEN 'Pennsylvania' ELSE NULL END

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, SUM(CASE WHEN statecode = 'MA' THEN 1 ELSE NULL END) AS TotalMassCustomers, AVG(CASE WHEN statecode = 'MA' THEN totalsales ELSE NULL END) AS TotalMassSales FROM dbo.Customer

Since NULL values are discarded when performing aggregate functions, we can easily get the required totals.