Learn more about SQL Server tools



solving sql server problems for millions of dbas and developers since 2006 attend our next webcast













































   Got a SQL tip?
            We want to know!

Using the CASE expression instead of dynamic SQL in SQL Server

MSSQLTips author Armando Prato By:   |   Read Comments (23)   |   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 (firstnamelastnamestatecodetotalsales
SELECT 'Thomas''Jefferson''VA'100.00

INSERT INTO dbo.Customer (firstnamelastnamestatecodetotalsales
SELECT 'John''Adams''MA'200.00

INSERT INTO dbo.Customer (firstnamelastnamestatecodetotalsales
SELECT 'Paul''Revere''MA'300.00

INSERT INTO dbo.Customer (firstnamelastnamestatecodetotalsales
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 ELSE NULL ENDAS TotalMassCustomers
AVG(CASE WHEN statecode 'MA' THEN totalsales ELSE NULL ENDAS 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 
customeridfirstnamelastnamestatecodestatedescriptiontotalsales
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(2NULL
AS
SET 
nocount ON

SELECT 
customeridfirstnamelastnamestatecodestatedescriptiontotalsales
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: 3/14/2008


About the author
MSSQLTips author Armando Prato
Armando Prato has over 24 years of industry experience and has been working with SQL Server since version 6.5.

View all my tips


print tip Print  
Become a paid author





join MSSQLTips for free SQL Server tips     



Learn more about SQL Server tools
Post a comment or let the author know this tip helped you.

       All comments are reviewed, so stay on subject or we may delete your comment.

*Name   *Email Notify for updates



       Note: your email address is not published. Required fields are marked with an asterisk (*)


Get free SQL tips:

*Enter Code refresh code     



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

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 Read The Tip

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 Read The Tip

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 Read The Tip

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 Read The Tip

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 Read The Tip

Collase is better than isnull.


Sunday, March 08, 2009 - 8:33:52 AM - jxrockwell Read The Tip
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 Read The Tip
[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 Read The Tip

 Hi

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 Read The Tip

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 Read The Tip

 I'd rather patrol left wing for an NHL team.


Saturday, March 22, 2008 - 4:18:42 PM - ssancetta Read The Tip

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 Read The Tip

 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 Read The Tip

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 Read The Tip

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 Read The Tip
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 Read The Tip

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 Read The Tip

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 Read The Tip

Did you do any performance analysis? Is the case expression faster than using dynamic SQL?


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

 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 Read The Tip

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 Read The Tip
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 Read The Tip

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 customeridfirstnamelastname
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





 
Sponsor Information







Copyright (c) 2006-2014 Edgewood Solutions, LLC All rights reserved
privacy | disclaimer | copyright | advertise | about
authors | contribute | feedback | giveaways | free t-shirt | user groups | community | events | first timer?
Some names and products listed are the registered trademarks of their respective owners.