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. In some cases, I am using a cursor 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 

SQL CASE Example for Updates

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 

SQL CASE Example for Selects

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.

SQL CASE Example for Order By

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)
AS
SET nocount ON
SELECT customerid, firstname, lastname, statecode, statedescription, totalsales
FROM dbo.Customer
ORDER BY 
CASE @sortdirection
     WHEN 'asc' THEN
      CASE @sortby 
       WHEN 'firstname' THEN firstname 
       WHEN 'lastname' THEN lastname 
       END
END 
ASC,
CASE @sortdirection
      WHEN 'desc' THEN
       CASE @sortby 
       WHEN 'firstname' THEN firstname 
       WHEN 'lastname' THEN lastname 
       END
END
DESC
GO
 
EXEC dbo.getCustomerData 'lastname', 'desc' 

SQL CASE Example for Order By

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
AS
SET nocount ON
SELECT customerid, firstname, lastname, statecode, statedescription, totalsales
FROM dbo.Customer
WHERE statecode = CASE WHEN @statecode IS NOT NULL THEN @statecode 
ELSE statecode
END
ORDER BY 
CASE @sortdirection
     WHEN 'asc' THEN
      CASE @sortby 
       WHEN 'firstname' THEN firstname 
       WHEN 'lastname' THEN lastname 
       END
END 
ASC,
CASE @sortdirection
      WHEN 'desc' THEN
       CASE @sortby 
       WHEN 'firstname' THEN firstname 
       WHEN 'lastname' THEN lastname 
       END
END
DESC
GO
 
EXEC dbo.getCustomerData 'lastname', 'desc', 'MA' 

Next Steps

• 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

MSSQL Tips

MSSQLTips.com was started in 2006 to provide SQL Server content about various aspects of SQL Server and other database platforms.