Click here to monitor SSC
SQLServerCentral is supported by Red Gate Software Ltd.
 
Log in  ::  Register  ::  Not logged in
 
 
 
        
Home       Members    Calendar    Who's On


Add to briefcase «««1234»»

Nesting Stored Procedures Expand / Collapse
Author
Message
Posted Thursday, May 22, 2003 10:17 AM
SSC Journeyman

SSC JourneymanSSC JourneymanSSC JourneymanSSC JourneymanSSC JourneymanSSC JourneymanSSC JourneymanSSC Journeyman

Group: General Forum Members
Last Login: Saturday, October 23, 2010 4:51 AM
Points: 80, 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




Post #62700
Posted Thursday, May 22, 2003 10:24 AM
SSC Eights!

SSC Eights!SSC Eights!SSC Eights!SSC Eights!SSC Eights!SSC Eights!SSC Eights!SSC Eights!

Group: General Forum Members
Last Login: Tuesday, November 5, 2013 9:05 AM
Points: 976, Visits: 59
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
Post #62701
Posted Monday, May 24, 2004 8:17 AM


SSC-Enthusiastic

SSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-Enthusiastic

Group: General Forum Members
Last Login: Tuesday, May 12, 2009 9:33 AM
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




Post #117334
Posted Friday, May 18, 2007 7:45 AM


SSC Veteran

SSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC Veteran

Group: General Forum Members
Last Login: Monday, July 7, 2014 12:50 PM
Points: 292, Visits: 264
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
Post #367182
Posted Friday, May 18, 2007 8:42 AM
SSC Veteran

SSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC Veteran

Group: General Forum Members
Last Login: Yesterday @ 2:35 PM
Points: 265, Visits: 2,565
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.
Post #367221
Posted Friday, May 18, 2007 1:34 PM
Forum Newbie

Forum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum Newbie

Group: General Forum Members
Last Login: Wednesday, June 4, 2014 7:20 AM
Points: 3, Visits: 107

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




Post #367337
Posted Friday, May 18, 2007 2:01 PM


Mr or Mrs. 500

Mr or Mrs. 500Mr or Mrs. 500Mr or Mrs. 500Mr or Mrs. 500Mr or Mrs. 500Mr or Mrs. 500Mr or Mrs. 500Mr or Mrs. 500

Group: General Forum Members
Last Login: Tuesday, June 21, 2011 10:03 AM
Points: 577, Visits: 102
I like the way you code, Robert, looking forward to seeing more articles.

Signature is NULL
Post #367346
Posted Friday, May 18, 2007 5:29 PM
Valued Member

Valued MemberValued MemberValued MemberValued MemberValued MemberValued MemberValued MemberValued Member

Group: General Forum Members
Last Login: Wednesday, June 25, 2014 4:22 PM
Points: 52, Visits: 405

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

Post #367376
Posted Friday, May 18, 2007 7:49 PM


Mr or Mrs. 500

Mr or Mrs. 500Mr or Mrs. 500Mr or Mrs. 500Mr or Mrs. 500Mr or Mrs. 500Mr or Mrs. 500Mr or Mrs. 500Mr or Mrs. 500

Group: General Forum Members
Last Login: Wednesday, January 25, 2012 8:14 AM
Points: 567, 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.
Post #367381
Posted Monday, June 4, 2007 11:42 AM
SSC Eights!

SSC Eights!SSC Eights!SSC Eights!SSC Eights!SSC Eights!SSC Eights!SSC Eights!SSC Eights!

Group: General Forum Members
Last Login: Tuesday, November 5, 2013 9:05 AM
Points: 976, Visits: 59
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
Post #371095
« Prev Topic | Next Topic »

Add to briefcase «««1234»»

Permissions Expand / Collapse