How many nested IF ELSE supported in SQL 2012

Question

How many nested IF ELSE supported in SQL 2012

khushbu

SSCarpal Tunnel

Points: 4591
More actions
September 26, 2012 at 12:34 am

#268243

Would like to know how many nested IF ELSE are suported in SQL 2012.
Are they also limited to edition?
I have SQL express which gives error on 125 nested IF ELSE.. but before making any confirmation i would like to know the correct value.
Tried seraching on MSDN link with no luck. Any pointers appreciated
Thanks

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

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

Grant Fritchey SSC Guru Points: 399058 More actions · Answer 1

It's limited based on the available memory.

But why would you be nesting IF/ELSE statements to the limit of your memory capacity? It strikes me that you might be going down a questionable path with your code.

"The credit belongs to the man who is actually in the arena, whose face is marred by dust and sweat and blood"
- Theodore Roosevelt

Author of:
SQL Server Execution Plans
SQL Server Query Performance Tuning

Sean Lange SSC Guru Points: 286602 More actions · Answer 2

Grant Fritchey (9/26/2012)
It's limited based on the available memory.
But why would you be nesting IF/ELSE statements to the limit of your memory capacity? It strikes me that you might be going down a questionable path with your code.

I would go so far as to say the code needs a complete rewrite/redesign. 125 nested IF/ELSE in any language is a clear sign that something is VERY wrong with the logic. :w00t:

_______________________________________________________________

Need help? Help us help you.

Read the article at http://www.sqlservercentral.com/articles/Best+Practices/61537/ for best practices on asking questions.

Need to split a string? Try Jeff Modens splitter http://www.sqlservercentral.com/articles/Tally+Table/72993/.

Cross Tabs and Pivots, Part 1 – Converting Rows to Columns - http://www.sqlservercentral.com/articles/T-SQL/63681/
Cross Tabs and Pivots, Part 2 - Dynamic Cross Tabs - http://www.sqlservercentral.com/articles/Crosstab/65048/
Understanding and Using APPLY (Part 1) - http://www.sqlservercentral.com/articles/APPLY/69953/
Understanding and Using APPLY (Part 2) - http://www.sqlservercentral.com/articles/APPLY/69954/

Michael Valentine Jones SSC Guru Points: 64818 More actions · Answer 3

I am trying to imagine a real world situation where IF/ELSE nested so deeply would be the way to go, instead of some other logical construct.

You should post the code to see if someone can point you in a better direction.

I'm not sure I actually want to see the code myself. :unsure:

Orlando Colamatteo SSC Guru Points: 182293 More actions · Answer 4

Michael Valentine Jones (9/26/2012)
I am trying to imagine a real world situation where IF/ELSE nested so deeply would be the way to go, instead of some other logical construct.
You should post the code to see if someone can point you in a better direction.
I'm not sure I actually want to see the code myself. :unsure:

The nastiest catch-all query in history that was refactored into IF ELSE branches instead of using sp_executesql :hehe:

There are no special teachers of virtue, because virtue is taught by the whole community.
--Plato

vivekkumar341 Say Hey Kid Points: 681 More actions · Answer 5

instead of having multiple if blocks, can't you use switch statements?

Sean Lange SSC Guru Points: 286602 More actions · Answer 6

vivekkumar341 (9/27/2012)
instead of having multiple if blocks, can't you use switch statements?

There is no switch statement in t-sql which I suspect is why there are so many nested if-else conditions.

_______________________________________________________________

Need help? Help us help you.

Read the article at http://www.sqlservercentral.com/articles/Best+Practices/61537/ for best practices on asking questions.

Need to split a string? Try Jeff Modens splitter http://www.sqlservercentral.com/articles/Tally+Table/72993/.

Cross Tabs and Pivots, Part 1 – Converting Rows to Columns - http://www.sqlservercentral.com/articles/T-SQL/63681/
Cross Tabs and Pivots, Part 2 - Dynamic Cross Tabs - http://www.sqlservercentral.com/articles/Crosstab/65048/
Understanding and Using APPLY (Part 1) - http://www.sqlservercentral.com/articles/APPLY/69953/
Understanding and Using APPLY (Part 2) - http://www.sqlservercentral.com/articles/APPLY/69954/

How many nested IF ELSE supported in SQL 2012

Cookies on SQLServerCentral