global temp table ....

[DLu]

Hi:

A regular permanent table is not an option:
Need to exec a procA which at the end also exec procB.

procA will insert to table A and also generate a intermidate result set to a ##A global table, it works fine at this point. However, when it exec the procB at the end of exec procA, the ##A global table is empty when entering procB.

The key is I want to pass a records set to the procB without using a tableTempB. Does a ## global table should be still alive until procB execution is done?

I also tried use table variable, but it does not look like to accept @tableA
as part of the procB's parameters.

Also tried function, but it could not support a #tempTable within it which will do a dynamic query insertion.

thanks
David

[Rawhide]

Yes, it should be. If it wasn't, the table wouldn't have been empty, you would have gotten an error stating that the object didn't exist. Look back at where you insert data into the table and find out why it didn't insert the data. Or if it did, find out where you deleted the data.

[MAK]

create procedure proca as
create table ##tablea (id int)
insert into ##tableA select 1
insert into ##tableA select 2
insert into ##tableA select 3
go

create procedure procb as
select * from ##tableA
go
exec procA
go
--inserts 3 rows in the temp table

--exec procB on the same connection
exec procB
--displays 3 rows


--exec procB on different connection
exec procB
--displays 3 rows

[DLu]

my mistake, it works.

in summarize:

a global table ## might be the only way to be used in procA (as a insert-dynamic query result set), and then call procB within procA. ProcB will also use the ## to process. So, the life of ## will be at the finishing of procA's execution.

a temp table # won't do it.
and a table variable could not do it.
I have tried to use talbe variable as part of the procB's parameter, it failed.

thanks
-D

[Joseph.Weng]

Why use global temporary table?
## temp table will be visible to all session.
According to your description,u can use session grade temp table.
ProcB also will see the ProcA's data if they are in same session.