Procedure and Temporary Table

Question

Procedure and Temporary Table

karthik M

One Orange Chip

Points: 29139
More actions
September 4, 2007 at 1:56 am

#120372

Hi experts,
shall we access a temporary table used in a procedure to some other procedure ?
Example :

Create Procedure P1
as
begin
select * from #temp
exec P2
End

Create Procedure P2
as
begin
create table #temp ( Eno int, Age int )
insert into #temp values (1,15000)
insert into #temp values (2,25000)
insert into #temp values (3,35000)
end

I have few question,
1) shall we access #temp in P1 , If yes how it is possible ? because # tables are session based.definitely p1 has one different session and p2 has some different session.
2&nbsp How to share a session ?
3) Explain me the internal working techiques ?
4) is it possible to insert values into #temp via p1 i.e within p1 will this statement work 'insert into #temp values (4,45000)' or not ?
5) How to know p1 and p2 sharing the same session ? is there any command or tools available in the market to know the session name or value ?
Can any experts give me exact output ?

Regards
Karthik

karthik

Viewing 4 posts - 1 through 3 (of 3 total)

You must be logged in to reply to this topic. Login to reply

Andras Belokosztolszki SSC-Insane Points: 22031 More actions · Answer 1

If you want to share a temporary table you could use global temporary tables. These have two # in their names, like ##foo.

These tables are created in the temp db. They will be automatically dropped if the initiating session has finished, and no other statement is referencing it.

Regards,

Andras

Andras Belokosztolszki, MCPD, PhD
GoldenGate Software

Andras Belokosztolszki SSC-Insane Points: 22031 More actions · Answer 2

You can read more about local and global temporary tables on http://msdn2.microsoft.com/en-us/library/ms174979.aspx

towards the middle of the page.

Andras

Andras Belokosztolszki, MCPD, PhD
GoldenGate Software

Gail Shaw SSC Guru Points: 1004504 More actions · Answer 3

It is possible what you're trying to do, but the other way around. this works

CREATE PROCEDURE p1 AS

CREATE TABLE #Temp (...)

EXEC p2

GO

CREATE PROCEDURE p2 AS

SELECT * FROM #Temp

GO

It doesn't work the other way around. you can't create temp tables in a sub proc and access them in the parent.

Watch out for global temp tables. Their lifetime is less well defined than local temp tables. Local temp tales go away when dropped or the connection that created them is closed.

Global temp tables hang around until nothing is accessing them. They're a good source of occational errors (create it when it's already there, or access it when its not)

As for a session identifier, are you're looking for the spid (select @@spid)

Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability

We walk in the dark places no others will enter
We stand on the bridge and no one may pass