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

SQLServerCentral is supported by Red Gate Software Ltd.

Popular Topics

Home

Members

Calendar

Who's On

Home » SQL Server 7,2000 » T-SQL » Maximum Number of "When Then" lines in a CASE...

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

Rate Topic

Display Mode

Topic Options

Author

Message

Roger Abram

Posted Saturday, May 31, 2008 12:46 PM

SSC-Enthusiastic

Group: General Forum Members
Last Login: Saturday, December 01, 2012 6:15 AM
Points: 132, Visits: 302

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

Post #509492

Matt Miller (#4)

Posted Saturday, May 31, 2008 1:09 PM

SSCertifiable

Group: General Forum Members
Last Login: Yesterday @ 4:20 PM
Points: 6,998, Visits: 13,947

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?

Post #509500

Jeffrey Williams 3188

Posted Saturday, May 31, 2008 1:23 PM

SSCarpal Tunnel

Group: General Forum Members
Last Login: Friday, May 17, 2013 11:21 AM
Points: 4,317, Visits: 9,216

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 opportunites brilliantly disguised as insurmountable obstacles.

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

Post #509501

Christian Buettner-167247

Posted Saturday, May 31, 2008 2:20 PM

SSCrazy

Group: General Forum Members
Last Login: Yesterday @ 1:06 AM
Points: 2,525, Visits: 3,618

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 Wink

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 Wink

Best Regards,
Chris Büttner

Post #509504

Carl Federl

Posted Saturday, May 31, 2008 2:21 PM

SSCrazy

Group: General Forum Members
Last Login: Yesterday @ 12:13 PM
Points: 2,224, Visits: 4,082

Based on a test, 512 WHEN are accepted.

SQL = Scarcely Qualifies as a Language

Post #509505

K. Brian Kelley

Posted Saturday, May 31, 2008 3:46 PM

Keeper of the Duck

Group: Moderators
Last Login: Today @ 1:55 PM
Points: 6,584, Visits: 1,789

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, CISA, MCSE, Security+, MVP - SQL Server
Regular Columnist (Security), SQLServerCentral.com
Author of Introduction to SQL Server: Basic Skills for Any SQL Server User
| Professional Development blog | Technical Blog |

LinkedIn | Twitter

Post #509522

« Prev Topic | Next Topic »

Permissions