Ask the Expert

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

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

There are Comments. Add yours.

 
TIP: Want to include a code block in your comment? Use <pre> or <code> tags around the desired text. Ex: <code>insert code</code>

REGISTER or login:

Forgot Password?
By submitting you agree to receive email from TechTarget and its partners. If you reside outside of the United States, you consent to having your personal data transferred to and processed in the United States. Privacy
Sort by: OldestNewest

Forgot Password?

No problem! Submit your e-mail address below. We'll send you an email containing your password.

Your password has been sent to: