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


Nesting Stored Procedures


Nesting Stored Procedures

Author
Message
Robert W Marda
Robert W Marda
SSCertifiable
SSCertifiable (5.8K reputation)SSCertifiable (5.8K reputation)SSCertifiable (5.8K reputation)SSCertifiable (5.8K reputation)SSCertifiable (5.8K reputation)SSCertifiable (5.8K reputation)SSCertifiable (5.8K reputation)SSCertifiable (5.8K reputation)

Group: General Forum Members
Points: 5787 Visits: 140
Comments posted to this topic are about the content posted at http://www.sqlservercentral.com/columnists/rmarda/nestingstoredprocedures.asp

Robert W. Marda
Billing and OSS Specialist - SQL Programmer
MCL Systems
Ian Yates
Ian Yates
SSCrazy Eights
SSCrazy Eights (9.4K reputation)SSCrazy Eights (9.4K reputation)SSCrazy Eights (9.4K reputation)SSCrazy Eights (9.4K reputation)SSCrazy Eights (9.4K reputation)SSCrazy Eights (9.4K reputation)SSCrazy Eights (9.4K reputation)SSCrazy Eights (9.4K reputation)

Group: General Forum Members
Points: 9446 Visits: 445
Also another reason for nesting stored procs is to avoid potential recompilation.

Eg, you might have a stored proc with 5 lines of code that can often cause recompilation...
It is better to have SQL server recompile a 20 line "child" SP (which contains those 5 lines) then to have SQL server compile a single 100 line SP repeatedly.

I like the other reason in the article though - one point of call for very similar functions Smile



Dave Poole
Dave Poole
SSC-Dedicated
SSC-Dedicated (31K reputation)SSC-Dedicated (31K reputation)SSC-Dedicated (31K reputation)SSC-Dedicated (31K reputation)SSC-Dedicated (31K reputation)SSC-Dedicated (31K reputation)SSC-Dedicated (31K reputation)SSC-Dedicated (31K reputation)

Group: General Forum Members
Points: 31848 Visits: 3663
I use nested stored procedures for the same reasons.

I quite often have
 
IF .....
INSERT #Tbl (Fields)
EXEC usp_SelectProc1 @Arg1, Arg2...Argn
ELSE
INSERT #Tbl (Fields)
EXEC usp_SelectProc2 @Arg1, Arg2...Argn

type routines.

The problem I have is that sometimes I want the usp_SelectProc stored procedures to use the INSERT/Exec method but you cannot nest this sort of functionality.

The comments I would make are that if you are going to nest stored procedures
  • You need good documentation/comments otherwise the dependencies can be hard to keep track of.

  • Always use field lists in your INSERT statements just incase the exec usp_SelectProc is altered to returb additional fields.


LinkedIn Profile
www.simple-talk.com
Frank Kalis
Frank Kalis
SSC-Dedicated
SSC-Dedicated (38K reputation)SSC-Dedicated (38K reputation)SSC-Dedicated (38K reputation)SSC-Dedicated (38K reputation)SSC-Dedicated (38K reputation)SSC-Dedicated (38K reputation)SSC-Dedicated (38K reputation)SSC-Dedicated (38K reputation)

Group: General Forum Members
Points: 38591 Visits: 289
I'm thinking of implementing centralised validation of input at a database level for protecting against sql injection. I do not trust this only be done at application level.
Nested stored procedures might be a way to do this.

Any thoughts on this?

Cheers,

Frank

--
Frank Kalis
Microsoft SQL Server MVP
Webmaster: http://www.insidesql.org/blogs
My blog: http://www.insidesql.org/blogs/frankkalis/
Dave Poole
Dave Poole
SSC-Dedicated
SSC-Dedicated (31K reputation)SSC-Dedicated (31K reputation)SSC-Dedicated (31K reputation)SSC-Dedicated (31K reputation)SSC-Dedicated (31K reputation)SSC-Dedicated (31K reputation)SSC-Dedicated (31K reputation)SSC-Dedicated (31K reputation)

Group: General Forum Members
Points: 31848 Visits: 3663
I take it that you are talking about a web application?

I have found that tasks that are too small to affect the performance of normal apps become massive overheads on web apps simply because of the amount of traffic a big site can have.

To give an example, my company stress tested one of our sites with varying numbers of simultaneous connections. The site was vastly more successful than was anticipated and we had to strip out some of the error reporting from our stored procedures in order to maintain performance. Under normal conditions this would have had no measurable affect, but with the sheer volumes of users it became an issue.

I would tend to put input validation in the client front end and in a middle tier on the web server probably enclosed within a DLL.

Validation of parameters (for web apps), once they have reached your database server should be kept as simple and as light weight as possible.

LinkedIn Profile
www.simple-talk.com
Frank Kalis
Frank Kalis
SSC-Dedicated
SSC-Dedicated (38K reputation)SSC-Dedicated (38K reputation)SSC-Dedicated (38K reputation)SSC-Dedicated (38K reputation)SSC-Dedicated (38K reputation)SSC-Dedicated (38K reputation)SSC-Dedicated (38K reputation)SSC-Dedicated (38K reputation)

Group: General Forum Members
Points: 38591 Visits: 289
quote:

I take it that you are talking about a web application?
[{quote]

GUI is displayed in Browser

quote:

I have found that tasks that are too small to affect the performance of normal apps become massive overheads on web apps simply because of the amount of traffic a big site can have.



No issue, because at the moment it is an internal app with a VERY limited number of user. It might be opened to affiliated companies in the future, but even then there are only about <50 regular users.

quote:

To give an example, my company stress tested one of our sites with varying numbers of simultaneous connections. The site was vastly more successful than was anticipated and we had to strip out some of the error reporting from our stored procedures in order to maintain performance. Under normal conditions this would have had no measurable affect, but with the sheer volumes of users it became an issue.



Just out of interest. About how many users do you speak?

[quote]
I would tend to put input validation in the client front end and in a middle tier on the web server probably enclosed within a DLL.

Validation of parameters (for web apps), once they have reached your database server should be kept as simple and as light weight as possible.



I hear you, but in case it is opened to affiliated companies it could happen that they develop their own GUI to access my db. And I don't know what comes then. Therefore my reflections.

Cheers,

Frank

--
Frank Kalis
Microsoft SQL Server MVP
Webmaster: http://www.insidesql.org/blogs
My blog: http://www.insidesql.org/blogs/frankkalis/
Dave Poole
Dave Poole
SSC-Dedicated
SSC-Dedicated (31K reputation)SSC-Dedicated (31K reputation)SSC-Dedicated (31K reputation)SSC-Dedicated (31K reputation)SSC-Dedicated (31K reputation)SSC-Dedicated (31K reputation)SSC-Dedicated (31K reputation)SSC-Dedicated (31K reputation)

Group: General Forum Members
Points: 31848 Visits: 3663
How many users? In excess of 30,000.

What you could do is write your GUI so that your main user interface is in a bald HTML file and that file is called in the context of your page furniture.

If someone else wants to write their own GUI they can do it but they are limited to specifying the stylesheet and page furniture.

Your server DLL and ASP/PHP/PL or whatever code still does all the work.

LinkedIn Profile
www.simple-talk.com
Frank Kalis
Frank Kalis
SSC-Dedicated
SSC-Dedicated (38K reputation)SSC-Dedicated (38K reputation)SSC-Dedicated (38K reputation)SSC-Dedicated (38K reputation)SSC-Dedicated (38K reputation)SSC-Dedicated (38K reputation)SSC-Dedicated (38K reputation)SSC-Dedicated (38K reputation)

Group: General Forum Members
Points: 38591 Visits: 289
quote:

What you could do is write your GUI so that your main user interface is in a bald HTML file and that file is called in the context of your page furniture.

If someone else wants to write their own GUI they can do it but they are limited to specifying the stylesheet and page furniture.



Hmm,...that sounds like something really worth trying. Do you have any recommendations, useful links, examples.. Or keywords that I can google?

TIA

Cheers,

Frank

--
Frank Kalis
Microsoft SQL Server MVP
Webmaster: http://www.insidesql.org/blogs
My blog: http://www.insidesql.org/blogs/frankkalis/
Robert W Marda
Robert W Marda
SSCertifiable
SSCertifiable (5.8K reputation)SSCertifiable (5.8K reputation)SSCertifiable (5.8K reputation)SSCertifiable (5.8K reputation)SSCertifiable (5.8K reputation)SSCertifiable (5.8K reputation)SSCertifiable (5.8K reputation)SSCertifiable (5.8K reputation)

Group: General Forum Members
Points: 5787 Visits: 140
David.Poole:

Can you provide me an example of what you want to do with INSERT/EXEC but can't with nesting stored procedures?

We don't often use INSERT/EXEC and so the focus of my article was not on that.

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
Robert W Marda
Robert W Marda
SSCertifiable
SSCertifiable (5.8K reputation)SSCertifiable (5.8K reputation)SSCertifiable (5.8K reputation)SSCertifiable (5.8K reputation)SSCertifiable (5.8K reputation)SSCertifiable (5.8K reputation)SSCertifiable (5.8K reputation)SSCertifiable (5.8K reputation)

Group: General Forum Members
Points: 5787 Visits: 140
ianyates:

Good point. Placing the code that forces recompilation in its own SP should reduce the time spent to recompile.

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
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