Calling another stored procedure's temp table

I am trying to create a stored procedure that will have a temporary table inside. Because I will need to be running recursive in the stored procedure, I have created another stored procedure that will call first stored procedure's temp table. But I failed to compile my second stored procedure because it always appears that the table cannot be found. Can you provide some information on how to call another stored procedure's temp table on execution?

    Requires Free Membership to View

    Login

The #table created by the first stored procedure needs to exist before the second stored procedure calling it can be created. Actually it is a rule that all the #tables referenced in a stored procedure must exist at the time of creating the stored procedure otherwise the stored procedure will not be created. This is irrespective of which stored procedure creates the #table initially.

The best practice is for you to create these objects in sequence in the same session as follows: 

create #<TABLE_NAME> (...)
go
create procedure <PROC1> AS ...
go
create procedurte <PROC2> AS ...
go
Please note that the #table must exist before either PROC1 or PROC2 can be created. It is also a good practice to ensure that PROC1 exists before PROC2 is created; otherwise you will get a warning.

This was first published in January 2005

Join the conversationComment

Share
Comments

    Results

    Contribute to the conversation

    All fields are required. Comments will appear at the bottom of the article.
    Back to top