Nesting Stored Procedures

  • Robert W Marda

    SSChampion

    Points: 13413

    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

    SSCoach

    Points: 19738

    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 🙂

  • David.Poole

    SSC Guru

    Points: 75198

    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.
  • Frank Kalis

    SSC Guru

    Points: 111183

    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/[/url]

  • David.Poole

    SSC Guru

    Points: 75198

    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.

  • Frank Kalis

    SSC Guru

    Points: 111183

    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?

    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/[/url]

  • David.Poole

    SSC Guru

    Points: 75198

    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.

  • Frank Kalis

    SSC Guru

    Points: 111183

    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/[/url]

  • Robert W Marda

    SSChampion

    Points: 13413

    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

    SSChampion

    Points: 13413

    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

  • Robert W Marda

    SSChampion

    Points: 13413

    Frank (A.K.A. a5xo3z1):

    The major danger I am aware of from sql injection attacks via data entered by a user and used in a stored procedure is when you dynamically build your queries and then execute them. The best way I know to completely eliminate this type of injection is to use the following code on your character datatypes: SET @LastName = REPLACE (@LastName, '''', '''''')

    That code ensures that all characters they enter will remain in the query to be compared against a column and can not execute as its own query.

    Other than that I agree that all other forms of validation will be best handled before the input reaches the stored procedures.

    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

  • David.Poole

    SSC Guru

    Points: 75198

    Robert,

    I don't have the specific example to hand as its in an archived project library.

    From what I remember I wrote code to produce a paginated list of records from a database

    Basically I had a stored procedure called usp_Paginator which accepted a page number, page length and recordset identifier.

    This stored procedure called another stored procedure depending on the recordset identifier which returned a recordset which I used to populate a temporary table.

    I came across and instance where I wanted to say

    INSERT MyTable

    exec usp_Proc @arg

    The problem was that usp_Proc contained code of this type itself.

    If you try and do this then SQL returns an error message warning you that you cannot nest these sorts of statements.

    As with all things SQL my INSERT/Exec method was a case of AN answer rather than THE answer.

  • Frank Kalis

    SSC Guru

    Points: 111183

    Hi Robert,

    quote:


    The major danger I am aware of from sql injection attacks via data entered by a user and used in a stored procedure is when you dynamically build your queries and then execute them. The best way I know to completely eliminate this type of injection is to use the following code on your character datatypes: SET @LastName = REPLACE (@LastName, '''', '''''')

    That code ensures that all characters they enter will remain in the query to be compared against a column and can not execute as its own query.

    Other than that I agree that all other forms of validation will be best handled before the input reaches the stored procedures.


    Agreed, I'm trying to avoid dynamic SQL for other than administrative tasks and to validate as much as possible at app level. But there is still a small percentage of uncertainty to deal with.

    You have to do more than just REPLACE (@LastName, '''', '''''')

    I'm sure you know this articles from http://www.appsecinc.com named Manipulating Microsoft SQL Server Using SQL Injection. Or Advanced SQL Injections in SQL Server Applications by http://www.nssoftware.com.

    But we're moving off-topic...

    One thing that can be stated is that you can use nested procs for this reason, or? If you do so, that's another question

    Cheers,

    Frank

    --
    Frank Kalis
    Microsoft SQL Server MVP
    Webmaster: http://www.insidesql.org/blogs
    My blog: http://www.insidesql.org/blogs/frankkalis/[/url]

  • Frank Kalis

    SSC Guru

    Points: 111183

    Correction,

    the second link should be called http://www.ngssoftware.com.

    Sorry!

    Cheers,

    Frank

    Edited by - a5xo3z1 on 05/22/2003 09:26:40 AM

    --
    Frank Kalis
    Microsoft SQL Server MVP
    Webmaster: http://www.insidesql.org/blogs
    My blog: http://www.insidesql.org/blogs/frankkalis/[/url]

  • johncyriac

    SSCommitted

    Points: 1542

    innovative thinking is not at all there

    just try this real nesting

    create proc nest_sp_dec

    @kount int

    as

    begin

    select @kount

    select @kount=@kount-1

    if @kount >0

    exec nest_sp_dec @kount

    end

    and execute it with

    nest_sp_dec 10

    How is it dude

Viewing 15 posts - 1 through 15 (of 31 total)

You must be logged in to reply to this topic. Login to reply