Best way to do nested case statement logic in SQL Server

I'm writing an SQL Query, where a few of the columns returned need to be calculated depending on quite a lot of conditions. I'm currently using nested case statements, but its getting messy. Is there a better (more organised and/or readable) way? (I am using Microsoft SQL Server, 2005) A simplified example:

SELECT col1, col2, col3, CASE WHEN condition THEN CASE WHEN condition1 THEN CASE WHEN condition2 THEN calculation1 ELSE calculation2 END ELSE CASE WHEN condition2 THEN calculation3 ELSE calculation4 END END ELSE CASE WHEN condition1 THEN CASE WHEN condition2 THEN calculation5 ELSE calculation6 END ELSE CASE WHEN condition2 THEN calculation7 ELSE calculation8 END END END AS 'calculatedcol1', col4, col5 -- etc FROM table

78.6k 17 17 gold badges 154 154 silver badges 201 201 bronze badges asked Feb 3, 2009 at 1:39 5,793 11 11 gold badges 39 39 silver badges 43 43 bronze badges Hi, did you use a tool (like SQLinForm) to indent the nested queries so nicely? Commented Jun 1, 2013 at 13:20 Appreciate you teaching me a nicer formatting method for nested CASE WHEN Commented Jan 8, 2015 at 6:39

There are certainly times when this would be efficient and helpful, but in general, I like to keep logic away from my SQL queries. Just a tip for posterity.

Commented Jul 13, 2018 at 19:50

10 Answers 10

You could try some sort of COALESCE trick, eg:

SELECT COALESCE( CASE WHEN condition1 THEN calculation1 ELSE NULL END, CASE WHEN condition2 THEN calculation2 ELSE NULL END, etc. )

answered Feb 3, 2009 at 1:44 4,992 3 3 gold badges 28 28 silver badges 35 35 bronze badges

Nice, I just tested to ensure that it short-circuits, and was suprised to find that it does. So if condition1 checked for a divide by zero, it appears that it's safe to do it in condition2. Not sure if this is guaranteed.

Commented Feb 3, 2009 at 3:34 One catch is that if one of your cases legitimately wants to return a NULL, it's no longer possible. Commented Feb 5, 2009 at 3:56

This is a great trick, but keep in mind that it may not perform as well as using CASE statements alone. This is "documented" in the Community Additions section here - msdn.microsoft.com/en-us/library/ms190349.aspx. My DBA just put the hammer down when I implemented this solution.

Commented Jan 25, 2013 at 16:54 When is this better than just a case statement (@beach's answer)? Commented Apr 4, 2014 at 17:28

How to rename a column in this answer.. e.g if I want to rename 'calculation1' to 'CAL1'. How is it possible syntactically ?

Commented Apr 12, 2017 at 6:38

Wrap all those cases into one.

SELECT col1, col2, col3, CASE WHEN condition1 THEN calculation1 WHEN condition2 THEN calculation2 WHEN condition3 THEN calculation3 WHEN condition4 THEN calculation4 WHEN condition5 THEN calculation5 ELSE NULL END AS 'calculatedcol1', col4, col5 -- etc FROM table

answered Jul 12, 2012 at 17:17 3,357 2 2 gold badges 24 24 silver badges 20 20 bronze badges

See this late answer below which is a mere copy but stresses that you do not have to write ELSE NULL which sometimes comes in handy.

Commented Aug 6 at 18:03

You can combine multiple conditions to avoid the situation:

CASE WHEN condition1 = true AND condition2 = true THEN calculation1 WHEN condition1 = true AND condition2 = false THEN calculation2 ELSE 'what so ever' END,

299 2 2 silver badges 12 12 bronze badges answered May 31, 2013 at 11:45 633 6 6 silver badges 14 14 bronze badges

I personally do it this way, keeping the embedded CASE expressions confined. I'd also put comments in to explain what is going on. If it is too complex, break it out into function.

SELECT col1, col2, col3, CASE WHEN condition THEN CASE WHEN condition1 THEN CASE WHEN condition2 THEN calculation1 ELSE calculation2 END ELSE CASE WHEN condition2 THEN calculation3 ELSE calculation4 END END ELSE CASE WHEN condition1 THEN CASE WHEN condition2 THEN calculation5 ELSE calculation6 END ELSE CASE WHEN condition2 THEN calculation7 ELSE calculation8 END END AS 'calculatedcol1', col4, col5 -- etc FROM table

answered Feb 3, 2009 at 1:58 8,570 4 4 gold badges 31 31 silver badges 26 26 bronze badges

Here's a simple solution to the nested "Complex" case statment: --Nested Case Complex Expression

select datediff(dd,Invdate,'2009/01/31')+1 as DaysOld, case when datediff(dd,Invdate,'2009/01/31')+1 >150 then 6 else case when datediff(dd,Invdate,'2009/01/31')+1 >120 then 5 else case when datediff(dd,Invdate,'2009/01/31')+1 >90 then 4 else case when datediff(dd,Invdate,'2009/01/31')+1 >60 then 3 else case when datediff(dd,Invdate,'2009/01/31')+1 >30 then 2 else case when datediff(dd,Invdate,'2009/01/31')+1 >30 then 1 end end end end end end as Bucket from rm20090131atb

Just make sure you have an end statement for every case statement

1,600 2 2 gold badges 18 18 silver badges 35 35 bronze badges answered Jul 17, 2009 at 14:13 Jims2885 Jims2885 You may want to format your answer if you want it to be read by others. Commented May 17, 2010 at 5:20 how is this an improvement on the method used in the original question? Commented Feb 9, 2021 at 23:40

a user-defined function may server better, at least to hide the logic - esp. if you need to do this in more than one query

answered Feb 3, 2009 at 1:42 Steven A. Lowe Steven A. Lowe 61k 19 19 gold badges 133 133 silver badges 204 204 bronze badges

We can combine multiple conditions together to reduce the performance overhead.

Let there are three variables a b c on which we want to perform cases. We can do this as below:

CASE WHEN a = 1 AND b = 1 AND c = 1 THEN '1' WHEN a = 0 AND b = 0 AND c = 1 THEN '0' ELSE '0' END,

answered Mar 9, 2015 at 8:07 Sanjeev Singh Sanjeev Singh 4,046 3 3 gold badges 35 35 silver badges 38 38 bronze badges This is the same as the [2 years older answer]( stackoverflow.com/a/16856534/11154841). Commented Aug 6 at 17:57

I went through this and found all the answers super cool, however wants to add to answer given by @deejers

 SELECT col1, col2, col3, CASE WHEN condition1 THEN calculation1 WHEN condition2 THEN calculation2 WHEN condition3 THEN calculation3 WHEN condition4 THEN calculation4 WHEN condition5 THEN calculation5 END AS 'calculatedcol1', col4, col5 -- etc FROM table

you can make ELSE optional as its not mandatory, it is very helpful in many scenarios.

answered Oct 16, 2017 at 10:50 user3065757 user3065757 493 1 1 gold badge 5 5 silver badges 14 14 bronze badges

right, you just drop here the line ELSE NULL , but that is still good to keep in mind that you can do it, which the other answer does not tell.

Commented Aug 6 at 18:01

This example might help you, the picture shows how SQL case statement will look like when there are if and more than one inner if loops

enter image description here

answered Jun 24, 2019 at 10:14 279 1 1 gold badge 2 2 silver badges 9 9 bronze badges

Please put the text of the code instead of screenshots because screenshots are hard to reuse or confirm.

Commented Feb 4, 2021 at 10:16

@Manik Not instead, but on top. The image is good to read, that can stay, and the code on top is good for internet searching and copying.

Commented Aug 6 at 17:59

Less complex format. Make sure keep the indentation as it is to not to be lost in ENDs.

SELECT CASE WHEN 1=1 THEN CASE WHEN 11=11 THEN CASE WHEN 111=111 THEN '1-11-111' END WHEN 12=12 THEN CASE WHEN 122=122 THEN '1-12-122' END WHEN 13=13 THEN CASE WHEN 133=133 THEN '1-13-133' END END ELSE CASE WHEN 2=2 THEN CASE WHEN 21=21 THEN CASE WHEN 211=211 THEN '2-21-211' END WHEN 22=22 THEN CASE WHEN 222=222 THEN '2-22-222' END END ELSE CASE WHEN 3=3 THEN CASE WHEN 31=31 THEN CASE WHEN 311=311 THEN '3-31-311' END WHEN 32=32 THEN CASE WHEN 322=322 THEN '3-32-322' END END ELSE CASE WHEN 4=4 THEN CASE WHEN 41=41 THEN CASE WHEN 411=411 THEN '4-41-411' END WHEN 42=42 THEN CASE WHEN 412=412 THEN '4-42-412' END END ELSE CASE WHEN 51=51 THEN CASE WHEN 511=511 THEN '51-511' END WHEN 52=52 THEN CASE WHEN 512=512 THEN '52-512' END WHEN 53=53 THEN CASE WHEN 513=513 THEN '53-513' END WHEN 54=54 THEN CASE WHEN 514=514 THEN '54-514' END END END END END END AS Sku FROM YourTable