Post reply

A nested Stored Procedure

  • February 10, 2004 at 3:15 am

    #107626

    Hi,

    I am trying to write a stored procedure that accesses another stored procedure which returns a result set at the end.  This result set is basically just one parameter.  I have noticed that you can create a temporary table to retrieve the result set, and have got this working.  But I have also read that this is bad for performance.

    How bad is this, and are there any alternatives?

     

    Thanks for any help

  • February 10, 2004 at 6:13 am

    #493360

    sfm,

    I just had a similar question.  I tried the #Temp route but when I had to write in 17000 records to the temp table it cost me 4 seconds.  This was unacceptable.  I then tried to find some info about nesting stored Procedures but never found much.

    I my case the temp table was bad for performance.  Try using derived tables, the are much faster!!

    Example:

    SELECT subtbl1.col1, subtbl1.col2 FROM ( Select col1, col2 From <table> ) subtbl1

    You must name the derived table.  If you don't name it you will get an error message.

    Good Luck

  • February 10, 2004 at 8:54 am

    #493403

    Use an output parameter rather than a result set.



    --Jonathan

Viewing 3 posts - 1 through 2 (of 2 total)

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