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 Friday, May 16, 2003 12:00 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 05, 2013 9:05 AM
Points: 976, Visits: 59
Comments posted to this topic are about the content posted at http://www.sqlservercentral.com/columnists/rmarda/nestingstoredprocedures.asp



Robert W. Marda
SQL Programmer
Ipreo
Post #12274
Posted Thursday, May 22, 2003 1:49 AM
Ten Centuries

Ten CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen Centuries

Group: General Forum Members
Last Login: Thursday, January 30, 2014 10:08 PM
Points: 1,038, Visits: 444
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 :)



Post #62681
Posted Thursday, May 22, 2003 2:07 AM
SSCrazy

SSCrazySSCrazySSCrazySSCrazySSCrazySSCrazySSCrazySSCrazy

Group: General Forum Members
Last Login: Today @ 3:49 PM
Points: 2,866, Visits: 1,708
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
Newbie on www.simple-talk.com
Post #62682
Posted Thursday, May 22, 2003 2:56 AM


SSCertifiable

SSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiable

Group: General Forum Members
Last Login: Tuesday, August 13, 2013 1:18 AM
Points: 5,956, Visits: 285
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/
Post #62683
Posted Thursday, May 22, 2003 3:11 AM
SSCrazy

SSCrazySSCrazySSCrazySSCrazySSCrazySSCrazySSCrazySSCrazy

Group: General Forum Members
Last Login: Today @ 3:49 PM
Points: 2,866, Visits: 1,708
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
Newbie on www.simple-talk.com
Post #62684
Posted Thursday, May 22, 2003 3:33 AM


SSCertifiable

SSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiable

Group: General Forum Members
Last Login: Tuesday, August 13, 2013 1:18 AM
Points: 5,956, Visits: 285
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/
Post #62685
Posted Thursday, May 22, 2003 3:50 AM
SSCrazy

SSCrazySSCrazySSCrazySSCrazySSCrazySSCrazySSCrazySSCrazy

Group: General Forum Members
Last Login: Today @ 3:49 PM
Points: 2,866, Visits: 1,708
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
Newbie on www.simple-talk.com
Post #62686
Posted Thursday, May 22, 2003 3:56 AM


SSCertifiable

SSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiable

Group: General Forum Members
Last Login: Tuesday, August 13, 2013 1:18 AM
Points: 5,956, Visits: 285
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/
Post #62687
Posted Thursday, May 22, 2003 8:47 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 05, 2013 9:05 AM
Points: 976, Visits: 59
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
SQL Programmer
Ipreo
Post #62688
Posted Thursday, May 22, 2003 8:50 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 05, 2013 9:05 AM
Points: 976, Visits: 59
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
SQL Programmer
Ipreo
Post #62689
« Prev Topic | Next Topic »

Add to briefcase 1234»»»

Permissions Expand / Collapse