Question about temporary table access 1

[thamms]

Hi,

I have a stored procedure (A) that calls a stored procedure (B).

Stored procedure B creates a local temporary table. My intention is for stored procedure A to access the temporary table created by B.

However, I am getting errors that A can't access the temp table B creates (doesn't exist).

My understanding is that the lifetime of the temp table is dependent on the connection that creates it.

So my question is, do A and B have their own connections? Otherwise, A should be able to see B's temp table until the connection is disconnected.

Thanks

According to BOL:

There are two types of temporary tables: local and global. Local temporary tables are visible only to their creators during the same connection to an instance of SQL Server as when the tables were first created or referenced. Local temporary tables are deleted after the user disconnects from the instance of SQL Server. Global temporary tables are visible to any user and any connection after they are created, and are deleted when all users that are referencing the table disconnect from the instance of SQL Server.

 

[gmmastros]

I think you're close. You just got things a little backwards. Here is what I recommend.

1. You should create the temp table in Procedure A.
2. Procedure B would add/update/delete records from the temp table.
3. Back in Procedure A, the temp table will still exist and you can do anything you want with the table.

Ex:

Alter Procedure ProcA
AS
Create Table #Blah(data Int)
Exec ProcB
insert Into #Blah Values(2)
Select * From #Blah
go

Alter Procedure ProcB
As
Insert Into #Blah Values(1)


go

Exec ProcA

-George

"The great things about standards is that there are so many to choose from." - Fortune Cookie Wisdom