(re)using a temporary table in a stored proc (was "Confusion")

Hi folks, I have a procedure that pefroms some action and creates the outputs to a temporary table #mytable. I want to call this procedure and take the results from #mytable within the procedure. Can i. If i call #mytable after executing the procedure; won't work. Means that the table gets dropped and doesn't prolong for the session?

Howdy!Yes..the local temp table is only around for the length of process...

A global temp table sticks around as long as it's referenced by any process...

So you can do something like this...basically wrapping your sprocs with a driver sproc

USE Northwind
GO

CREATE PROC mySproc00
AS
BEGIN
UPDATE ##myGlobalTemp99 SET CustomerId = 'VINET'
END
GO

CREATE PROC mySproc99
AS
BEGIN
SELECT * INTO ##myGlobalTemp99 FROM Orders

SELECT TOP 10 * FROM ##myGlobalTemp99

EXEC mySproc00

SELECT TOP 10 * FROM ##myGlobalTemp99

DROP TABLE ##myGlobalTemp99

END
GO

EXEC mySproc99
GO

DROP PROC mySproc00
DROP PROC mySproc99
GO|||Hi, sir. thanx for helpin around. there's a little problem though. The procedure will be executed by many users concurrently; i get error; table already exists when older session persist and another executes the procedure.

Any other guidlines!

Howdy!|||post what the table looks like...and what the sproc is suppose to be doing...|||Instead of Temp table, declare table type variable and work with that.

Madhivanan|||I believe he needs to pass and store data between processing threads...table variables won't let you do that.

Why I asked for the Table Layout is this.

Each Process gets it's own unique spid.

I would create a permanent table, and and a column for the spid.

Capture the spid, from a driver sproc, and for every row in the process make sure you use that spid.

Just make sure you clear out the rows for your spid before you start the process.|||Yes, Brett; that idea helped. thanx. :)

Howdy!|||Depending on the volum of activity...I might consider a partitioned view using the spid as the partioning range in different files and on different drives...

I'll take a look into this...|||I hate when people are so dogmatic in their statements. What would happen if you run this code? DON'T RUN IT, just answer the questions first!

create proc sp_1 as selct * from #t
go
create proc sp_2 as
ceate table #t (f1 int null)
insert #t select 1 union select 2
exec sp_1
go
exec sp_2
go
drop proc sp_1, sp_2
go

Here, regardless of how many users execute sp_2 simulteneously, they will always see only their temporary tables.|||Doooh...it's the same spid

As long as it's all referenced in the same driver your temp table is isolated..I got confused that it was with separate threads

Even with that...what I suggested wouldn't buy you anything anyway

Thanks for pointing that out...|||Doooh...Even with that...what I suggested wouldn't buy you anything anyway...So why did you suggest it? :D|||Cause I'm a moron...

It's like flying down the freeway...I missed the exit

You could use that method to pass data to a trigger though...|||So if that is not causing the problem he is having, then what is?

If i call #mytable after executing the procedure; won't work.

This seems to indicate he wants to access the table through a different connection...|||If i call #mytable after executing the procedure; won't work. Means that the table gets dropped and doesn't prolong for the session?Actually it looks like the same connection, unless his definition of a "session" is different...|||It's like flying down the freeway...I missed the exit...Don't feel bad, I use turn-arounds very often :D|||Then maybe he should post his code, 'cause something must be going wrong...|||He's probably creating the temp in the nested proc

The temp needs to be created in the driver

I imagine this is what's going on

create proc sp_2 as
create table #t (f1 int null)
insert #t select 1 union select 2
go

create proc sp_1 as
EXEC sp_2
select * from #t
go

exec sp_1
go

drop proc sp_1, sp_2
go|||Hi folks; u've been so kind! Sorry for getting back late;

I've a proc proc1 that performs some routine and outputs a temporary table #tab1. I want this table be accessed through other procs or within a batch that calls this procedure; WITHIN THE SAME CONNECTION(SESSION,SPID :))

The only way around i could find was the SPID idea into a permanent table.

The proc1 is a standard routine and many other procs or scripts need the output from it; i wonder if i could use function that returns a table... that could be used further.

Howdy|||actually i need to collect the record from a few join table. and then calculate the score and group the date in weekly basic.

so now i able to select all the data from the source, and then use case to create a column call weekGroup. but i fail to group weekGroup for the source. so i plan to create temporary table and store all the record that i select from the join table and then do again the select statement from the temporary table to group the data again.

anyone can help me?:eek:

regards
terence chua|||actually i need to collect the record from a few join table. and then calculate the score and group the date in weekly basic.

so now i able to select all the data from the source, and then use case to create a column call weekGroup. but i fail to group weekGroup for the source. so i plan to create temporary table and store all the record that i select from the join table and then do again the select statement from the temporary table to group the data again.

anyone can help me?:eek:

regards
terence chua

First read Brett's sticky on top of the forum.|||...and start a new thread rather than resurrecting this old one...