Calling a stored procedure in a stored procedure

Question

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.

Answer 1

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?

Answer 2

I agree with everything gbn said.

The critical bit in his comment, is "if it wasn't critical for performance". For the vast majority of OLTP operations, it really doesn't matter how many levels of nesting you have (assuming that your data model and procedural layer is designed intelligently so that you've got a normalized data model and that you're not adding layers of calls for no good reason) because it really doesn't matter if it takes an extra couple hundredths of a second to insert a new Foo row and all the associated Bar and Mark rows. The ability to reuse the Bar logic later on more than makes up for the extra overhead of calling a few more stored procedures.

When you are talking about data warehousing, however, where you need to quickly process millions of Foo rows in a relatively tight load window, procedural logic can become problematic. A hundredth of a second per row, multiplied by 10 million rows, for example, is 100,000 seconds= 1667 minutes= 27.8 hours. In that sort of environment, performance is much more critical and you're far more likely to choose a set-based approach rather than calling a lot of smaller, more modular procedures.