Can I pass a temporary table from one procedure to another?

[agc1976]

Hello,

I have 2 procedures:

Proc1
Proc2

I want Proc1 to call Proc2. Proc2 will generate a temporary table and return the results to Proc1 for further processing.

Is this possible? If not, what alternatives do I have?

Thank you,
Alfredo

 

[tlbroadbent]

Use a global temporary table. Specify global with ## rather than #.

 

[agc1976]

But that global table will be available to multiple users, right?

I will have multiple users executing the procedure and I want each to have his own temporary table. Any other suggestion?

 

[dbmsguy]

you could dynamically name the ## temp table to include something like the spid so that it would be unique to the process. Paul

http://www.dbmsguy.com

 

[arrowhouse]

Is the creation of your table a simple select (or union of selects)?

If so you could use a derived table (which is often more efficient too).

In brief syntax is
SELECT ... FROM
[table1]
INNER (or whatever) JOIN
(SELECT ... FROM {ie this should be what is in your proc 1}) as 'derived_table_name'
ON derived_table_name.column_name1 = table1.column_name ...
WHERE ...

If you have any aggregate/calculated columns that you refer to you will need to give them a name so you can refer to them in the query.

Unless you have a good reason for wanting to use a temporary table.

Can you give us more details?

Good luck

 

[vechalapu]

Hi,

1. create #table in Proc A
2. Create #table in Proc B before create procedure statement to take care of compilation
3. During executation time, you have access to #table created in Proc A.

 

[agc1976]

Thanks to everyone, I used vechalapu's suggestoin ... it worked without step 2. Step 2 returned an error but without it I could still access the table previously created in ProcA

 

[agc1976]

Arrowhouse, I missed your post. Thanks for your suggestion, but my subquery is way too complex (involves a lot of logical decisions) and I needed a procedure to evaluate some stuff.

Anyways, my problem is solved : )