Q

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?

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

Dig deeper on Linux administration tools

Have a question for an expert?

Please add a title for your question

Get answers from a TechTarget expert on whatever's puzzling you.

You will be able to add details on the next page.

0 comments

Oldest 

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:

-ADS BY GOOGLE

SearchDataCenter

SearchServerVirtualization

SearchCloudComputing

SearchEnterpriseDesktop

Close