Click here to monitor SSC
SQLServerCentral is supported by Redgate
 
Log in  ::  Register  ::  Not logged in
 
 
 


Nesting Stored Procedures


Nesting Stored Procedures

Author
Message
johncyriac
johncyriac
SSC Journeyman
SSC Journeyman (90 reputation)SSC Journeyman (90 reputation)SSC Journeyman (90 reputation)SSC Journeyman (90 reputation)SSC Journeyman (90 reputation)SSC Journeyman (90 reputation)SSC Journeyman (90 reputation)SSC Journeyman (90 reputation)

Group: General Forum Members
Points: 90 Visits: 58
rmarda

nest level exceeds 32 that only I wanted to point out ,is there an y explanation from ms,
I am having a script to convert figures to words using the same concept I will pulish it later ,thak ye rmarda

with
love john



Robert W Marda
Robert W Marda
SSC Eights!
SSC Eights! (984 reputation)SSC Eights! (984 reputation)SSC Eights! (984 reputation)SSC Eights! (984 reputation)SSC Eights! (984 reputation)SSC Eights! (984 reputation)SSC Eights! (984 reputation)SSC Eights! (984 reputation)

Group: General Forum Members
Points: 984 Visits: 82
I don't know that Microsoft has explained why you can only nest to nest level 32. I suspect they wanted to set some limit otherwise you could just have a stored procedure call itself and it would never stop. My guess is that an SP calling itself to infinity would cause other problems that would be worse than simply limiting the nest level to 32 and giving you an error if you exceed it.

Robert W. Marda
SQL Programmer
bigdough.com
The world’s leading capital markets contact database and software platform.



Robert W. Marda
SQL Programmer
Ipreo

steven powell
steven powell
SSC-Enthusiastic
SSC-Enthusiastic (157 reputation)SSC-Enthusiastic (157 reputation)SSC-Enthusiastic (157 reputation)SSC-Enthusiastic (157 reputation)SSC-Enthusiastic (157 reputation)SSC-Enthusiastic (157 reputation)SSC-Enthusiastic (157 reputation)SSC-Enthusiastic (157 reputation)

Group: General Forum Members
Points: 157 Visits: 2

one good trick i know of is related to validation of arguments...

let's say you have some procs which are called by the GUI, so you need to do some validation of the rubbish the users type into the system... These procs call other procs (which are not used by the GUI)... in these internal procs you have a couple of lines like this

if @@nestlevel = 1 
begin
-- raise an error in your normal way...
end

Which means that no-one can call your code from an ODBC connection through excel or access or whatever Strictly speaking you don't even need to grant exec permissions to these "internal" procs because all the tables are owned by dbo (just like the calling procs) and as an added benefit you can totally trust the arguments you receive because they come from other code in the database...

cheers,
ste





Tatsu
Tatsu
Old Hand
Old Hand (302 reputation)Old Hand (302 reputation)Old Hand (302 reputation)Old Hand (302 reputation)Old Hand (302 reputation)Old Hand (302 reputation)Old Hand (302 reputation)Old Hand (302 reputation)

Group: General Forum Members
Points: 302 Visits: 307
I used nested procedures a lot to reduce server round-trips. If I am loading a .NET DataSet with several tables to be loaded I will call a single procedure that, in turn, EXECUTEs all the other procedures I need to load those tables.

Bryant E. Byrd, BSSE MCDBA MCAD
Business Intelligence Administrator
MSBI Administration Blog
JJ B
JJ B
Old Hand
Old Hand (327 reputation)Old Hand (327 reputation)Old Hand (327 reputation)Old Hand (327 reputation)Old Hand (327 reputation)Old Hand (327 reputation)Old Hand (327 reputation)Old Hand (327 reputation)

Group: General Forum Members
Points: 327 Visits: 2846
Jeaux wrote: "My question is why couldn't you rewrite the example..." I have a different answer to this question. I once had a stored procedure with a similar structure to the one you gave as an example. The problem was that (if I understand correctly) SQL Server comes up with an optimized plan for the first query it runs the first time it compiles.

For example: Let's say SQL Server first compiles and optimizes based on the first query. Later a user runs the proc and the IF takes runs the second query. Now it is possible that the second query runs like a turtle-sooo sloowwwwww.

That's the exact problem I had once, and it almost brought my server down. I had a bunch of users running the same proc but hitting one of 5 queries that weren't optimized. I turned it into one master proc calling 6 different baby procs. Voila! Problem solved. It had nothing to do with writing clean code. It was a matter of optimization. Fair disclosure (but any bad explanations are mine): I got this trick off of Kimberly Tripp's site.
dsully
dsully
Forum Newbie
Forum Newbie (3 reputation)Forum Newbie (3 reputation)Forum Newbie (3 reputation)Forum Newbie (3 reputation)Forum Newbie (3 reputation)Forum Newbie (3 reputation)Forum Newbie (3 reputation)Forum Newbie (3 reputation)

Group: General Forum Members
Points: 3 Visits: 116

Just finished reading the article on nested SPs, and it raised a question about a practice I have been following for a few years now.

I write my SPs for one Web application in a common SP with the CommonSP;1, CommonSP;2 etc... and this helps me organize my work by application.

I am wondering if Robert Marda, or others have any thoughts about the wisdom, or not, of this practice.

David





Calvin Lawson
Calvin Lawson
Mr or Mrs. 500
Mr or Mrs. 500 (582 reputation)Mr or Mrs. 500 (582 reputation)Mr or Mrs. 500 (582 reputation)Mr or Mrs. 500 (582 reputation)Mr or Mrs. 500 (582 reputation)Mr or Mrs. 500 (582 reputation)Mr or Mrs. 500 (582 reputation)Mr or Mrs. 500 (582 reputation)

Group: General Forum Members
Points: 582 Visits: 102
I like the way you code, Robert, looking forward to seeing more articles.

Signature is NULL
David Rueter
David Rueter
SSC-Enthusiastic
SSC-Enthusiastic (106 reputation)SSC-Enthusiastic (106 reputation)SSC-Enthusiastic (106 reputation)SSC-Enthusiastic (106 reputation)SSC-Enthusiastic (106 reputation)SSC-Enthusiastic (106 reputation)SSC-Enthusiastic (106 reputation)SSC-Enthusiastic (106 reputation)

Group: General Forum Members
Points: 106 Visits: 573

Robert,

It might be interesting in a follow-up article to consider the implications and pitfalls of explicit transactions in nested stored procedures.

David Rueter
drueter@assyst.com


einman33
einman33
Mr or Mrs. 500
Mr or Mrs. 500 (575 reputation)Mr or Mrs. 500 (575 reputation)Mr or Mrs. 500 (575 reputation)Mr or Mrs. 500 (575 reputation)Mr or Mrs. 500 (575 reputation)Mr or Mrs. 500 (575 reputation)Mr or Mrs. 500 (575 reputation)Mr or Mrs. 500 (575 reputation)

Group: General Forum Members
Points: 575 Visits: 512
Can someone talk about transactions a little? If transaction control resides in the app tier, would this approach increase transactional locking that may be uneeded? Like the article, thanks.
Robert W Marda
Robert W Marda
SSC Eights!
SSC Eights! (984 reputation)SSC Eights! (984 reputation)SSC Eights! (984 reputation)SSC Eights! (984 reputation)SSC Eights! (984 reputation)SSC Eights! (984 reputation)SSC Eights! (984 reputation)SSC Eights! (984 reputation)

Group: General Forum Members
Points: 984 Visits: 82
I am glad you have liked my article. Please forgive me for being slow to reply to your comments. I have been busy with many personal matters but hope to be able to write more articles soon. Thank you for your ideas, I will keep them in mind.



Robert W. Marda
SQL Programmer
Ipreo

Go


Permissions

You can't post new topics.
You can't post topic replies.
You can't post new polls.
You can't post replies to polls.
You can't edit your own topics.
You can't delete your own topics.
You can't edit other topics.
You can't delete other topics.
You can't edit your own posts.
You can't edit other posts.
You can't delete your own posts.
You can't delete other posts.
You can't post events.
You can't edit your own events.
You can't edit other events.
You can't delete your own events.
You can't delete other events.
You can't send private messages.
You can't send emails.
You can read topics.
You can't vote in polls.
You can't upload attachments.
You can download attachments.
You can't post HTML code.
You can't edit HTML code.
You can't post IFCode.
You can't post JavaScript.
You can post emoticons.
You can't post or upload images.

Select a forum

































































































































































SQLServerCentral


Search