Calling a stored procedure in a stored procedure

My question is

Calling a stored proc within a stored proc within another stored proc. When is this type of development detrimental?

In order to promote code reusablity we have encapsulated various insert/update statements into stored procedures. So if want to insert A Foo record then you would pass the parameters to the CreateFoo stored proc and go about your day.

Well lets assume a Foo object needs to Create A Bar objects which also Creates a Mark object.

Thus your stored proc will call the foo stored proc which will then call the bar stored proc which will then call the mark stored proc.

When is this not a good idea, and what are my other options. Thank you very muc开发者_StackOverflowh. If you could, could you put the sources that back up your answer. Again thank you very much.

It is not good or bad - if you need to call the one procedure from the another, just do it.

Implementing logic on database level in stored procedures is not a good way of doing things in most of the cases:

• it makes your application depends on this particular DBMS
• it is more complex to scale the application on database layer then to scale it on the middle layer.
• it complicates debugging (unless you can place a breakpoint in your SP)
• SP language (SQL) usually is not flexible and powerful as middle layer language

But for some applications (and it may be your case) it is better to use SP - when the cons are beaten by the pros: simple and quick implementation of the logic on the language you know better.