Using the CASE expression instead of dynamic SQL in SQL Server
By: Armando Prato | Comments (28) | Related: 1 | 2 | 3 | 4 | 5 | 6 | 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
(
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.
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)
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'
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
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
- 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
About the author
Armando Prato has close to 30 years of industry experience and has been working with SQL Server since version 6.5.
View all my tips