Post reply

Stored Procedure within stored procedure

  • December 30, 2003 at 4:03 pm

    #83277

    I have a stored procedure sp_A that calls another stored procedure sp_B using EXEC. sp_B returns a table which I would like to use in sp_A. Is there any way other than cursors to do this like using a temporary table or something like that.

    I would really appretiate if someone could show me an example of how to do it.

    Thanks,

    NS

  • December 30, 2003 at 4:08 pm

    #488303

    Create a #Temp table in sp_A, then fill it up in sp_B.



    Once you understand the BITs, all the pieces come together

  • December 30, 2003 at 4:09 pm

    #488305

    Could you please show me an example.

  • December 30, 2003 at 4:09 pm

    #488304

    You can do this, but there is a minor problem. The scope of a temperary table is limited to the procedure that it is declared in.

    If you declare the temperary table in procedure B it will be gone after the procedure returns. You can declare it in Proc_A and then populate it in proc_B.

  • December 30, 2003 at 4:17 pm

    #488306

    Create Procedure sp_B as

    Insert into #Temp Select 1

    go

    Create Procedure sp_a as

    Create Table #Temp (ANumber Int)

    Exec sp_B -- puts a rec in #Temp

    Select * from #Temp

    go

    Exec sp_A -- should show a result table



    Once you understand the BITs, all the pieces come together

  • December 30, 2003 at 4:22 pm

    #488307

    Figured out. Thanks for the prompt reply.

Viewing 6 posts - 1 through 5 (of 5 total)

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