SQL Clone
SQLServerCentral is supported by Redgate
 
Log in  ::  Register  ::  Not logged in
 
 
 


Nesting Stored Procedures


Nesting Stored Procedures

Author
Message
johncyriac
johncyriac
Old Hand
Old Hand (308 reputation)Old Hand (308 reputation)Old Hand (308 reputation)Old Hand (308 reputation)Old Hand (308 reputation)Old Hand (308 reputation)Old Hand (308 reputation)Old Hand (308 reputation)

Group: General Forum Members
Points: 308 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
SSCrazy
SSCrazy (2.9K reputation)SSCrazy (2.9K reputation)SSCrazy (2.9K reputation)SSCrazy (2.9K reputation)SSCrazy (2.9K reputation)SSCrazy (2.9K reputation)SSCrazy (2.9K reputation)SSCrazy (2.9K reputation)

Group: General Forum Members
Points: 2937 Visits: 113
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
Billing and OSS Specialist - SQL Programmer
MCL Systems
steven powell
steven powell
SSC Veteran
SSC Veteran (233 reputation)SSC Veteran (233 reputation)SSC Veteran (233 reputation)SSC Veteran (233 reputation)SSC Veteran (233 reputation)SSC Veteran (233 reputation)SSC Veteran (233 reputation)SSC Veteran (233 reputation)

Group: General Forum Members
Points: 233 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
SSCommitted
SSCommitted (1.6K reputation)SSCommitted (1.6K reputation)SSCommitted (1.6K reputation)SSCommitted (1.6K reputation)SSCommitted (1.6K reputation)SSCommitted (1.6K reputation)SSCommitted (1.6K reputation)SSCommitted (1.6K reputation)

Group: General Forum Members
Points: 1644 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
Ten Centuries
Ten Centuries (1.2K reputation)Ten Centuries (1.2K reputation)Ten Centuries (1.2K reputation)Ten Centuries (1.2K reputation)Ten Centuries (1.2K reputation)Ten Centuries (1.2K reputation)Ten Centuries (1.2K reputation)Ten Centuries (1.2K reputation)

Group: General Forum Members
Points: 1161 Visits: 2860
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
SSCrazy
SSCrazy (2.1K reputation)SSCrazy (2.1K reputation)SSCrazy (2.1K reputation)SSCrazy (2.1K reputation)SSCrazy (2.1K reputation)SSCrazy (2.1K reputation)SSCrazy (2.1K reputation)SSCrazy (2.1K reputation)

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

Signature is NULL
David Rueter
David Rueter
SSChasing Mays
SSChasing Mays (612 reputation)SSChasing Mays (612 reputation)SSChasing Mays (612 reputation)SSChasing Mays (612 reputation)SSChasing Mays (612 reputation)SSChasing Mays (612 reputation)SSChasing Mays (612 reputation)SSChasing Mays (612 reputation)

Group: General Forum Members
Points: 612 Visits: 574

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
Ten Centuries
Ten Centuries (1K reputation)Ten Centuries (1K reputation)Ten Centuries (1K reputation)Ten Centuries (1K reputation)Ten Centuries (1K reputation)Ten Centuries (1K reputation)Ten Centuries (1K reputation)Ten Centuries (1K reputation)

Group: General Forum Members
Points: 1033 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
SSCrazy
SSCrazy (2.9K reputation)SSCrazy (2.9K reputation)SSCrazy (2.9K reputation)SSCrazy (2.9K reputation)SSCrazy (2.9K reputation)SSCrazy (2.9K reputation)SSCrazy (2.9K reputation)SSCrazy (2.9K reputation)

Group: General Forum Members
Points: 2937 Visits: 113
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
Billing and OSS Specialist - SQL Programmer
MCL Systems
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