Click here to monitor SSC
SQLServerCentral is supported by Redgate
 
Log in  ::  Register  ::  Not logged in
 
 
 


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


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

Author
Message
Roger Abram
Roger Abram
SSC-Enthusiastic
SSC-Enthusiastic (139 reputation)SSC-Enthusiastic (139 reputation)SSC-Enthusiastic (139 reputation)SSC-Enthusiastic (139 reputation)SSC-Enthusiastic (139 reputation)SSC-Enthusiastic (139 reputation)SSC-Enthusiastic (139 reputation)SSC-Enthusiastic (139 reputation)

Group: General Forum Members
Points: 139 Visits: 318
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
Matt Miller (#4)
Matt Miller (#4)
SSCertifiable
SSCertifiable (7.7K reputation)SSCertifiable (7.7K reputation)SSCertifiable (7.7K reputation)SSCertifiable (7.7K reputation)SSCertifiable (7.7K reputation)SSCertifiable (7.7K reputation)SSCertifiable (7.7K reputation)SSCertifiable (7.7K reputation)

Group: General Forum Members
Points: 7662 Visits: 18092
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 3188
Jeffrey Williams 3188
SSCarpal Tunnel
SSCarpal Tunnel (4.5K reputation)SSCarpal Tunnel (4.5K reputation)SSCarpal Tunnel (4.5K reputation)SSCarpal Tunnel (4.5K reputation)SSCarpal Tunnel (4.5K reputation)SSCarpal Tunnel (4.5K reputation)SSCarpal Tunnel (4.5K reputation)SSCarpal Tunnel (4.5K reputation)

Group: General Forum Members
Points: 4468 Visits: 9836
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
Problems are opportunities brilliantly disguised as insurmountable obstacles.

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

Christian Buettner-167247
Christian Buettner-167247
SSCrazy
SSCrazy (3K reputation)SSCrazy (3K reputation)SSCrazy (3K reputation)SSCrazy (3K reputation)SSCrazy (3K reputation)SSCrazy (3K reputation)SSCrazy (3K reputation)SSCrazy (3K reputation)

Group: General Forum Members
Points: 2951 Visits: 3889
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
Carl Federl
SSCrazy
SSCrazy (2.3K reputation)SSCrazy (2.3K reputation)SSCrazy (2.3K reputation)SSCrazy (2.3K reputation)SSCrazy (2.3K reputation)SSCrazy (2.3K reputation)SSCrazy (2.3K reputation)SSCrazy (2.3K reputation)

Group: General Forum Members
Points: 2342 Visits: 4349
Based on a test, 512 WHEN are accepted.

SQL = Scarcely Qualifies as a Language
K. Brian Kelley
K. Brian Kelley
Keeper of the Duck
Keeper of the Duck (6.8K reputation)

Group: Moderators
Points: 6826 Visits: 1917
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
terrencepierce
Forum Newbie
Forum Newbie (2 reputation)Forum Newbie (2 reputation)Forum Newbie (2 reputation)Forum Newbie (2 reputation)Forum Newbie (2 reputation)Forum Newbie (2 reputation)Forum Newbie (2 reputation)Forum Newbie (2 reputation)

Group: General Forum Members
Points: 2 Visits: 1
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
Lowell
SSChampion
SSChampion (14K reputation)SSChampion (14K reputation)SSChampion (14K reputation)SSChampion (14K reputation)SSChampion (14K reputation)SSChampion (14K reputation)SSChampion (14K reputation)SSChampion (14K reputation)

Group: General Forum Members
Points: 14979 Visits: 39001
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!

Go


Permissions

You can't post new topics.
You can't post topic replies.
You can't post new polls.
You can't post replies to polls.
You can't edit your own topics.
You can't delete your own topics.
You can't edit other topics.
You can't delete other topics.
You can't edit your own posts.
You can't edit other posts.
You can't delete your own posts.
You can't delete other posts.
You can't post events.
You can't edit your own events.
You can't edit other events.
You can't delete your own events.
You can't delete other events.
You can't send private messages.
You can't send emails.
You can read topics.
You can't vote in polls.
You can't upload attachments.
You can download attachments.
You can't post HTML code.
You can't edit HTML code.
You can't post IFCode.
You can't post JavaScript.
You can post emoticons.
You can't post or upload images.

Select a forum

































































































































































SQLServerCentral


Search