Maximum Number of "When Then" lines in a CASE statement?

Question

Maximum Number of "When Then" lines in a CASE statement?

Roger Abram

Hall of Fame

Points: 3683
More actions
May 31, 2008 at 12:46 pm

#72165

Does anyone know if there's a maximum number of "When xxx THEN yyy" lines you can have within a single CASE statement?
Thanks.
Roger

Viewing 8 posts - 1 through 7 (of 7 total)

You must be logged in to reply to this topic. Login to reply

Matt Miller (4) SSC Guru Points: 124210 More actions · Answer 1

I can't say I know, but at the risk of sounding pedantic - if you have to ask the question, you should be considering a lookup table.....

----------------------------------------------------------------------------------
Your lack of planning does not constitute an emergency on my part...unless you're my manager...or a director and above...or a really loud-spoken end-user..All right - what was my emergency again?

Jeffrey Williams SSC Guru Points: 90303 More actions · Answer 2

I don't know if there is a limit - at least, I cannot find anything that says there is. However, when I find that I have a very large statement I start to look at other ways to perform this operation.

For example, if I am converting some internal value/code to a human readable value - I will create a table instead and use the table.

I guess it really depends on what you are trying to do.

Jeff

Jeffrey Williams
“We are all faced with a series of great opportunities brilliantly disguised as impossible situations.”

― Charles R. Swindoll

How to post questions to get better answers faster
Managing Transaction Logs

Christian Buettner-167247 SSChampion Points: 13729 More actions · Answer 3

I know it does not provide any value (as already pointed out, a lookup table should be used), but I get an overflow with 9712 WHEN cases.

I just had to try this out 😉

SELECT CASE 'Hello'

WHEN '' THEN ''

.... 9710 more

WHEN '' THEN ''

END

But you can add more cases within these like

SELECT CASE 'Hello'

WHEN '' THEN

CASE 'Hello2'

WHEN '' THEN ''

.... x more

WHEN '' THEN ''

.... 9710 more

WHEN '' THEN ''

END

Now it's up to you to derive some value from this exercise 😉

Best Regards,

Chris Büttner

Carl Federl One Orange Chip Points: 25384 More actions · Answer 4

Based on a test, 512 WHEN are accepted.

SQL = Scarcely Qualifies as a Language

K. Brian Kelley SSC Guru Points: 114642 More actions · Answer 5

Matt Miller (5/31/2008)
I can't say I know, but at the risk of sounding pedantic - if you ask to ask the question, you should be considering a lookup table.....

Aye. Just because you can do something doesn't mean it's a great idea.

K. Brian Kelley
@kbriankelley

terrencepierce Valued Member Points: 64 More actions · Answer 6

Hey, I appreciate the test. I have a list of thousands of entries (people have a habit of typing the same thing very different ways) that I need to examine and then categorize. Initially I was looking at a monster CASE using LIKE to collapse the list. I guess it depends how much commonality there is to be found.:-D

Lowell SSC Guru Points: 323518 More actions · Answer 7

terrencepierce (11/21/2013)
Hey, I appreciate the test. I have a list of thousands of entries (people have a habit of typing the same thing very different ways) that I need to examine and then categorize. Initially I was looking at a monster CASE using LIKE to collapse the list. I guess it depends how much commonality there is to be found.:-D

yeah definitely sounds like using a lookup table would be better;

then you can join on the "WHEN" entry value to the lookup table, and get the translated "THEN" value from the table.

Lowell

--help us help you! If you post a question, make sure you include a CREATE TABLE... statement and INSERT INTO... statement into that table to give the volunteers here representative data. with your description of the problem, we can provide a tested, verifiable solution to your question! asking the question the right way gets you a tested answer the fastest way possible!