Using the CASE expression instead of dynamic SQL in SQL Server

By: Armando Prato | Read Comments (25) | Related Tips: 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  
( 
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

Last Update: 2008-03-14

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

Related Resources

Become a paid author

Friday, July 21, 2017 - 4:13:06 PM - Rabia

I have three multi statement table valued functions that are calling a table that has one column with strings of 5000 lines and those 3 functions call the table with one column and the functions serve the purpose of returning a table by separating the 5000 lines strings into different values by a delimiter (of question mark) into separate fields.

I cross apply when i call the function with the table of 5000 lines of strings separated by delimitter in SQL server but what i am looking for is to define a stored procedure that has all these 3 functions that return a table and then that return table values are to be compared with the 3 tables i have besides the one all three functions are calling.

How do i accomplish that?

Monday, February 02, 2015 - 3:15:16 PM - Francisco Lopez

nice dude!

i have already done that (just Example 4) but i forgot all the posibilities!

Friday, October 12, 2012 - 12:03:20 PM - Scott C

For an exhaustive discussion of this topic: http://www.sommarskog.se/dyn-search-2005.html

And I do mean exhaustive. I just re-read it, and I'm exhausted.

Thursday, September 13, 2012 - 10:04:19 AM - Richard Schaefer

Your first example of using CASE to translate codes to values seems to be the least effective way to do this. The whole point of relational databases is the ability to join sets of data. If your original data only has a code and you need the associated value then set up a reference table (i.e. "stateValues") with the code and value and join it to your original table on statecode in a view or other query. If you really want the state value in the base table (i.e. you need to index it) then set up a computed, persisted field that retrieves the value from the stateValues table.

Wednesday, September 05, 2012 - 4:33:43 PM - Ryan	Back To Top
Seems you need to have SET ANSI_NULLS OFF for some of your examples to work. Not sure if that was mentioned or assumed somewhere.

Tuesday, July 17, 2012 - 10:22:19 AM - Pete	Back To Top
Where all does this work? I tried using it with the FROM clause and it bombed: Select x FROM Case When ...

Tuesday, May 08, 2012 - 2:14:09 PM - Linda Leslie

NOTE: If you use dynamic SQL, you must give permissions to the underlying table in addition to the stored procedure. This is my main reason for avoiding dynamic SQL - security reasons. I also use the version listed above and have not had performance issues but my database is only 17 GB:

WHERE CASE WHEN (@statecode IS NULL) THEN 1
ELSE
CASE WHEN (state = @statecode) THEN 1
ELSE 0 END
END = 1

Tuesday, May 08, 2012 - 3:46:39 AM - kk	Back To Top
Collase is better than isnull.

Sunday, March 08, 2009 - 8:33:52 AM - jxrockwell

I've been using SQL similar to what is listed in this page to get a dynamic order by for some time.

My stored proc operates on tables that have a few, but not a ton (e.g. ~100k) records in them. I was recently attempting to do some performance troubleshooting on this query and have found the case statement in the order by is causing problems.

Here is the original code. In my case I was actually passing in the full string and then ordering as appropriate:


ORDER BY 
	CASE @SortExpression
		WHEN 'MyColA DESC' THEN MyColA 
	END DESC,
	CASE @SortExpression 
		WHEN 'MyColA ASC' THEN MyColA 
	END ASC,
	CASE @SortExpression 
		WHEN 'MyColB DESC' THEN MyColB 
	END DESC

I tried changing this to an int thinking it might be the varchar slowing it down?

ORDER BY 
	CASE @SortExpression
		WHEN 1 THEN MyColA 
	END DESC

But , that didn't work. It is however much faster if I take out the parameter all together. However, this obviously won't work because I need to be able to pass in the parameter to trigger the correct order by.


ORDER BY 
	CASE 1
		WHEN 1 THEN MyColA 
	END DESC

Has anyone else epxerienced this sort of thing? Will the dynamic SQL though ugly improve the performance even though it has to be re-compiled?

Wednesday, May 28, 2008 - 1:51:50 AM - SergeyS

[quote user="unclebiguns"]I then duplicated the stored procedure with one using dynamic sql to append the appropriate where clause based on the parameters and executed it using sp_executesql.

Performance was much better based using the dynamic SQL. The SP using dynamic SQL used less CPU, ran faster, and took fewer reads in each scenario.

Mainly because the optimizer had to use the non-clustered index on last_name, first_name in every case while the dynamic SQL was able to use the appropriate index. [/quote]I doubt the first part of the last sentence is true. Have a look at all those mentioned

WHERE (@statecode IS NULL OR state = @statecode)
WHERE state = ISNULL(@statecode,state)
WHERE state = COALESCE(@statecode,state)

They simply do not use indexes at all, they just blatantly loop through all the records to check the condition instead of immediate fetching the record using an index.
The author of the article should rewrite it in order not to confuse newbies how CASE expression should be used correctly. The correct usage would be sort of:

WHERE CASE WHEN (@statecode IS NULL) THEN 1
ELSE
CASE WHEN (state = @statecode) THEN 1
ELSE 0 END
END = 1

Friday, March 28, 2008 - 12:14:24 PM - aprato

It's great that the article piqued your curiousity enough to toy around with some scenarios.....That's the idea behind all these tips!

There's no substitute for a good WHERE clause.... I've used CASE in a WHERE with small tables (less than a hundred rows where a scan would've been performed by the optimzer anyway) but you're right, this is an instance where dynamic sql is superior. For large sets of data, it likely will be for the reason you note. My biggest bang for the buck comes with conditional logic in UPDATEs where I can eliminate cursor processing. I've also used it a bit in reporting procedures to create dynamic ORDER BYs. In WHERE clauses, I use it once in a great while and only if it's a small table (like a code table that doesn't change it's list of values much, if at all). Testing always bears out the approach to take.

Friday, March 28, 2008 - 11:30:43 AM - unclebiguns

I had read your article and did some testing on a stored procedure designed to find a person from my persons table. The stored procedure had 3 nullable paramters, @person_id int, @last_name nvarchar(20), @first_name nvarchar(20). The query I used using case is:

Select P.person_id, P.last_name, P.first_name, P.middle_name, P.birth_date From dbo.persons P Where P.person_id = Case When @person_id Is Null Then P.person_id Else @person_id End And P.last_name Like Case When @last_name Is Null Then P.last_name Else @last_name + '%'End And P.first_name Like Case When @first_name Is Null Then P.first_name Else @first_name + '%' End

I then duplicated the stored procedure with one using dynamic sql to append the appropriate where clause based on the parameters and executed it using sp_executesql. Performance was much better based using the dynamic SQL. The SP using dynamic SQL used less CPU, ran faster, and took fewer reads in each scenario. Mainly because the optimizer had to use the non-clustered index on last_name, first_name in every case while the dynamic SQL was able to use the appropriate index. For example when passing in just @person_id the SP using Case had these stats:

Table 'Persons'. Scan count 1, logical reads 257, physical reads 5, read-ahead reads 251, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.

While the SP with dynamic SQL had these stats:

Table 'Persons'. Scan count 0, logical reads 2, physical reads 2, read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.

These kind of performance differences make me lean toward dynamic SQL over Case in the Where clause when you have optional parameters.

Saturday, March 22, 2008 - 4:32:14 PM - aprato	Back To Top
I'd rather patrol left wing for an NHL team.

Saturday, March 22, 2008 - 4:18:42 PM - ssancetta	Back To Top
Thanks for the CLEAR explanation... Time for you to retire to a life of textbook writing.

Thursday, March 20, 2008 - 3:42:47 PM - aprato

Hi Juan... Thanks for the alternate approach!

That's definitely another way of approaching the WHERE clause it but you may find that the execution plans may be similar.

Either way, for small sets you gain some flexibility. For large sets of data, you may or may not get a
performant query. As with all queries, it would need testing against your dataset to check for acceptable performance.

Thursday, March 20, 2008 - 3:19:25 PM - aprato

How would you replace the expressions via dynamic SQL? The CASE expression is evaluating the value of a column and returning another value.

Hi Jay

Consider the ORDER BY in the example.... another way to get a dynamic order by

select @sql = 'select * from customer where lastname = ''gates'' order by '

if @firstname is not null
select @sql = @sql + 'firstname'

else
select @sql = @sql + 'lastname'

if @asc_or_desc = 'desc'
select @sql = @sql + ' desc'

then you'd execute the statement with EXEC or sp_executesql

As you can see, it gets a little ugly

I'm not a big fan of dyamic sql for a variety of reasons and when I do use it, I will use sp_executesql with parameters
to try to get a reusable, parameterized execution plan

Thursday, March 20, 2008 - 12:22:16 PM - wynden

There are several alternatives to the CASE here...
SELECT .............
FROM ..........
WHERE (@statecode IS NULL OR state = @statecode)

SELECT .............
FROM ..........
WHERE state = ISNULL(@statecode,state)

SELECT .............
FROM ..........
WHERE state = COALESCE(@statecode,state)

All of these (as well as the sample using CASE) generate identical execution plans, so performance is not an issue. I prefer the ISNULL or COALESCE versions for brevity and clarity.

Thursday, March 20, 2008 - 12:17:41 PM - martino	Back To Top
I agree -- nice article. However, instead of dynamic SQL I use coalesce SELECT customerid, firstname, lastname, statecode, statedescription, totalsales FROM dbo.Customer WHERE (statecode=coalesce(@statecode, statecode))

Thursday, March 20, 2008 - 10:41:34 AM - juan

Great Article,

However, case is not suitable for all occasions. When performance matters I would use this, below intead of posted version.

Modiffied version:

SELECT customerid, firstname, lastname, statecode, statedescription, totalsales FROM dbo.Customer

WHERE (@statecode IS NULL OR statecode = @statecode)

Posted version:

SELECT customerid, firstname, lastname, statecode, statedescription, totalsales FROM dbo.Customer

WHERE statecode = CASE WHEN @statecode IS NOT NULL THEN @statecode ELSE statecode END

Thursday, March 20, 2008 - 10:11:57 AM - jaybutler

How would you replace the expressions via dynamic SQL? The CASE expression is evaluating the value of a column and returning another value.

I would stay was from dynamic SQL anyhow at all costs. It will cause stored procedures to recompile every time. That could be a performance killer. There could be security implications as well. Each user needs to be granted access directly to all referenced object rather than implicitly gaining access to them via a stored procedure.

Thursday, March 20, 2008 - 6:39:30 AM - Gazza	Back To Top
Did you do any performance analysis? Is the case expression faster than using dynamic SQL?

Sunday, March 16, 2008 - 8:03:15 AM - aprato

Hi Jay

The good folks at Edgewood have amended the article to use the correct terminology. Thanks for keeping me honest - I missed it!

Saturday, March 15, 2008 - 4:22:00 PM - aprato

Hi Jay

You're semantically correct... thanks for catching that. I've discussed with others as a function, as a statement, and as an expression. I tend to refer to it generically with peers as a statement but it is actually an expression. I'll have the article amended to use the correct terminology.

Thanks again.

Saturday, March 15, 2008 - 8:32:05 AM - jaybutler	Back To Top
Good article. But, CASE is not a statement in Transact SQL. It is an expression. Big difference.

Friday, March 14, 2008 - 2:18:59 PM - ffoozle Back To Top

Great article!

I took the idea of using a case one step further and used it as part of a BETWEEN operator (which I wasn't sure was allowed). You can use it like this (assuming, that you extend your example to have a hiredate which represents the date of hire, and an inclusive date range represented by @startdate and @enddate):

SELECT customerid, firstname, lastname FROM dbo.Customer WHERE hiredate BETWEEN CASE WHEN @startdate IS NOT NULL THEN @startdate ELSE hiredate END

AND CASE WHEN @enddate IS NOT NULL THEN @startdate ELSE hiredate END

Tutorials DBA Dev BI Career Categories Webcasts Whitepapers Today's Tip Join

Tutorials DBA Dev BI Categories Webcasts

DBA Dev BI Categories

Using the CASE expression instead of dynamic SQL in SQL Server

By: Armando Prato | Read Comments (25) | Related Tips: 1 | 2 | 3 | 4 | More > Dynamic SQL

About the author

Post a comment or let the author know this tip helped.