Temp table loses data - SQL Server Forums

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

General SQL Server Forums

New to SQL Server Programming

Temp table loses data

Author

Topic

netspider
Starting Member

5 Posts

Posted - 2007-09-26 : 13:27:41

Not sure if you can help on this but Ive got a stored procedure in sql server and it creates a temp table. I then call another stored procedure from this one. When it returns to the 1st stored procedure I want the temp table to keep the information entered into the table, but the data is lost.

Is there a flag that can be turned on and off do this?

Or can you suggest anything else

Regards

Steve

Steve Fouracre

tkizer
Almighty SQL Goddess

38200 Posts

Posted - 2007-09-26 : 13:30:56

You'd have to post your code in order for us to help.

Tara Kizer
Microsoft MVP for Windows Server System - SQL Server
http://weblogs.sqlteam.com/tarad/

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2007-09-26 : 13:31:09

It's called SCOPE.
Temp tables created in called SP is destroyed when exiting.

You can use ##Temp (global temp table), but it's hard to maintain in a multi-user environment.

E 12°55'05.25"
N 56°04'39.16"

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2007-09-26 : 13:37:23

This won't work

create procedure a1
as

set nocount on

exec a2
select * from #a
go

create procedure a2
as

set nocount on

create table #a (i int)

insert #a
select 1
go

exec a1

E 12°55'05.25"
N 56°04'39.16"

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2007-09-26 : 13:37:47

This will work

create procedure b1
as

set nocount on

create table #b (i int)
exec b2
select * from #b
go

create procedure b2
as

set nocount on

insert #b
select 2
go

exec b1

E 12°55'05.25"
N 56°04'39.16"

tkizer
Almighty SQL Goddess

38200 Posts

Posted - 2007-09-26 : 13:38:10

If the temp table is created from the parent sproc, then it will exist in the child sproc too as the child is in scope.

Tara Kizer
Microsoft MVP for Windows Server System - SQL Server
http://weblogs.sqlteam.com/tarad/

netspider
Starting Member

5 Posts

Posted - 2007-09-26 : 14:47:24

Thanks for all your comments, I think Ive found a flaw not in the sql but in the php providing the info to the database

Regards

Steve

Steve Fouracre

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources