Using the CASE expression instead of dynamic SQL in SQL Server

By:   |   Comments (28)   |   Related: 1 | 2 | 3 | 4 | 5 | 6 | > 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.

fig 1

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' 

fig 2

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' 

fig 3

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


sql server categories

sql server webinars

subscribe to mssqltips

sql server tutorials

sql server white papers

next tip



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

This author pledges the content of this article is based on professional experience and not AI generated.

View all my tips



Comments For This Article




Friday, March 2, 2018 - 12:24:54 PM - Joseph M. Morgan Back To Top (75337)

 I have long wondered whether a join to a lookup table or a case statement will perform better. If you had a states tables, with the 2-letter code as a primary key, and you joined to the abbreviation, would that run faster than a case statement? Also, I use the join technique whenever the case staements start to swell. We started with a few brands, so setting a brand code via case statements was easy. Now we hae 15, and the SQL gets unkempt. What does your experience suggest?

 

Joey

 


Thursday, August 10, 2017 - 2:07:12 PM - DATTA KUMAR K S RAO Back To Top (64492)

This is the error message I am getting for your information :

 

Msg 102, Level 15, State 1, Procedure ELG_SUMMARY_STORE_PROCEDURE, Line 31

Incorrect syntax near '='.

Msg 102, Level 15, State 1, Procedure ELG_SUMMARY_STORE_PROCEDURE, Line 36

Incorrect syntax near 'ELG_SUMMARY'.

Msg 102, Level 15, State 1, Procedure ELG_SUMMARY_STORE_PROCEDURE, Line 95

Incorrect syntax near 'END'.

 


Thursday, August 10, 2017 - 2:02:36 PM - DATTA KUMAR K S RAO Back To Top (64491)

Hi Armando

 

I am trying to migrate from DB2 to SQL Server, I am having issues and can you please through some light on this as to how to go about?  Thanks

CREATE PROCEDURE ELG_SUMMARY_STORE_PROCEDURE ( @in_Member_SSN VARCHAR(9),

                                               @in_Subgroup CHARACTER(7),

                                               @in_Employer VARCHAR(9),

                                               @in_Benefit_Plan CHARACTER(7),

                                               @in_Plan_Type VARCHAR(5),

                                               @in_Coverage_Code CHARACTER(5),

                                               @in_Member_Only CHARACTER(3),

                                               @in_BEN_DATE DATE,

                                               @in_END_DATE DATE)

as

------------------------------------------------------------------------

-- SQL Stored Procedure

    -- in_Subgroup

    -- in_Employer

    -- in_Benefit_Plan

    -- in_Plan_Type

    -- in_Coberage_Code

    -- in_Member_Only

    -- in_add_date

------------------------------------------------------------------------

BEGIN

    -- Declare cursor

 

    DECLARE cursor1 CURSOR FOR

        SELECT *

           FROM BASYS.ELG_SUMMARY AS ELG_SUMMARY

           WHERE

 

                ELG_SUMMARY.MEMBER_SSN =

                CASE

                WHEN CASE(@in_Member_SSN) = 'ALL'

                THEN ELG_SUMMARY.MEMBER_SSN

                ELSE @in_Member_SSN

                END AND

 

                ELG_SUMMARY.SUBGROUP =

                CASE

                WHEN CASE(@in_Subgroup) = 'ALL'

                THEN ELG_SUMMARY.SUBGROUP

                ELSE CASE(@in_Subgroup)

                END and

 

                ELG_SUMMARY.BENEFIT_PLAN =

                CASE

                WHEN CASE(@in_Benefit_Plan) = 'ALL'

                THEN ELG_SUMMARY.BENEFIT_PLAN

                ELSE CASE(@in_Benefit_Plan)

                END and

 

                ELG_SUMMARY.PLAN_TYPE =

                CASE

                WHEN CASE(@in_Plan_Type) = 'ALL'

                THEN ELG_SUMMARY.PLAN_TYPE

                ELSE CASE(@in_Plan_Type)

                END and

 

                ELG_SUMMARY.EMPLOYER =

                CASE

                WHEN CASE(@in_Employer) = 'ALL'

                THEN ELG_SUMMARY.EMPLOYER

                ELSE @in_Employer

                END and

 

                ELG_SUMMARY.COVERAGE_CODE =

                CASE

                WHEN CASE(@in_Coverage_Code) = 'ALL'

                THEN ELG_SUMMARY.COVERAGE_CODE

                ELSE @in_Coverage_Code

                END and

 

                ELG_SUMMARY.DEP_NUMBER =

                CASE

                        WHEN @in_Member_Only = 'YES'

                        THEN '0'

                        WHEN @in_Member_Only = 'NO'

                        THEN ELG_SUMMARY.DEP_NUMBER

                END and

 

                ELG_SUMMARY.BEN_DATE <=

                CASE

                        WHEN @in_BEN_DATE IS NULL

                        THEN DATE('2019-02-01')

                        ELSE @in_BEN_DATE

                END AND

 

                ELG_SUMMARY.END_DATE >=

                CASE

                        WHEN @in_END_DATE IS NULL

                        THEN DATE('2000-07-31')

                        ELSE @in_END_DATE

                END

                ;

    -- Cursor left open for client application

    OPEN cursor1;

END

                                                                               


Friday, July 21, 2017 - 4:13:06 PM - Rabia Back To Top (60010)

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 2, 2015 - 3:15:16 PM - Francisco Lopez Back To Top (36136)

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 Back To Top (19895)

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 Back To Top (19505)

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 5, 2012 - 4:33:43 PM - Ryan Back To Top (19402)

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 (18578)

Where all does this work? I tried using it with the FROM clause and it bombed: Select x FROM Case When ...


Tuesday, May 8, 2012 - 2:14:09 PM - Linda Leslie Back To Top (17343)

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 8, 2012 - 3:46:39 AM - kk Back To Top (17327)

Collase is better than isnull.


Sunday, March 8, 2009 - 8:33:52 AM - jxrockwell Back To Top (2951)
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 Back To Top (1049)
[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 Back To Top (795)

 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 Back To Top (794)

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 (771)

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


Saturday, March 22, 2008 - 4:18:42 PM - ssancetta Back To Top (770)

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 Back To Top (760)

 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 Back To Top (759)

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 Back To Top (758)

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 (757)
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 Back To Top (756)

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 Back To Top (755)

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 (754)

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


Sunday, March 16, 2008 - 8:03:15 AM - aprato Back To Top (733)

 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 Back To Top (732)

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 (731)
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 (730)

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
















get free sql tips
agree to terms