CODEWITHSUNDEEP
sql-server-2005stored-procedures
Sreedhar Danturthi
Sreedhar Danturthi

Reputation: 7581

Regarding nested stored procedure

Is it a good practice to have nested stored procedures (meaning calling a stored procedure inside another stored procedure)? Are there any precautions that one should follow while nesting stored procedures? Is there a permissible limit on how many one can nest inside?

Thanks in anticipation

Upvotes: 2

Views: 1796

Answers (2)

Andriy M
Andriy M

Reputation: 77707

Yes, there is a limitation on the level of nesting. From the manual:

You can nest stored procedures […] up to 32 levels.

The fact that the limitation exists might imply that nesting stored procedure calls too much isn't a very good thing. Yet the maximum level seems to me big enough for one not to worry about nesting calls like 5 or so levels deep.

And indeed, I've never heard that having nested calls in your design introduced drawbacks of any kind, nor have I experienced any actual disadvantage from calling stored procedures from one another. On the contrary, I think, factoring your server-side code enables you to make your design more flexible. Or, if nothing else, it at least allows you to make your stored procedures' code more manageable by splitting the larger ones into more moderate-sized (and possibly logically complete) units.

I do not mean to say that you should always try to factor your stored procedures at any cost, I just wouldn't see anything wrong with your having to nest your procedures if that would fit your design better.

Upvotes: 3

Mikael Eriksson
Mikael Eriksson

Reputation: 138990

The max nesting level is 32 Nesting Stored Procedures.
In my opinion, the real downside of doing this is that it quickly gets out of control what SP is dependent on another SP. The risk is that you change something in one SP that has unforeseen effects in another SP. Once that has bitten you a couple of times you hesitate before changing the SP and considers creating a new one that does almost the exact same thing and after a while no one knows why the SP is there or if it is actually being used. Gets easily quite messy.

Upvotes: 4

Related Questions