Environment sql server 2005
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 much.
If you could, could you put the sources that back up your answer. Again thank you very much.
Best Answer
This can be good thing.
If you have to create bar and mark objects for other reasons or in other place then having a reusable stored proc eases maintenance and allows all checks in one place.
If I have to create n bar objects I'd probably loop n times rather than doing it set based if it wasn't critical for performance. Maintainability wins out for me. Of course, maybe my stored proc should deal with 1 to n bar creations... but then again did I know this up front? :-)
My answer here shows you how to nest stored procs with error handling: What is the best method to add error handling in SQL 2005 stored procs?