Take the 2-minute tour ×
Database Administrators Stack Exchange is a question and answer site for database professionals who wish to improve their database skills and learn from others in the community. It's 100% free, no registration required.

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.

share|improve this question

2 Answers 2

up vote 4 down vote accepted

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?

share|improve this answer
    
when does this turn into a bad thing? if i do nest stored procs like this 10 times is it bad? 20 times? is there some sort of magic number of nested procs when it is better to yank the code out of one stored proc and run it inline? Is it one of those things where i have to just run independent tests and find out what works in my scenario? –  george9170 Apr 25 '11 at 18:56
    
More than 4 or 5 levels would be a red flag for me. Check out @@NESTLEVEL too –  gbn Apr 25 '11 at 19:21

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.

share|improve this answer
    
er 1667 minutes = a day and a bit. But you've added bits that I should have otherwise... thanks –  gbn Apr 26 '11 at 16:25
    
Er, yeah. How about that :-) I guess I need to return to remedial 3rd grade to cover that whole concept of "telling time." I guess there is a unit between minutes and days. I've corrected my answer-- thanks for the catch. –  Justin Cave Apr 26 '11 at 16:47

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

Not the answer you're looking for? Browse other questions tagged or ask your own question.