Regarding the Temporary Table - 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

SQL Server 2000 Forums

SQL Server Development (2000)

Regarding the Temporary Table

Author

Topic

AskSQLTeam
Ask SQLTeam Question

0 Posts

Posted - 2005-03-09 : 08:05:38

Vishwas A writes "hi ,
I want to access a temporary table which is populated in first stored procedure in second stored procedure. I am executing the first SP and trying to Access the Temp table in second stored procedure. It is giving error "server: Msg 208, Level 16, State 1, Procedure sp_Check_Exception, Line 18
Invalid object name '#temp'."

Thanks in Advance

Regards
Vishwas A"

RM
Yak Posting Veteran

65 Posts

Posted - 2005-03-09 : 08:26:40

Instead of creating a local temporay table #table create a global temporary table ##table. But beware other connections will also have access to this globle temp table.

BTW what are you trying to do with the temp table?

RM
Yak Posting Veteran

65 Posts

Posted - 2005-03-09 : 08:36:09

I tried this...

Create proc proca
as
Begin
create table #a (a int)
insert into #a select 1
insert into #a select 2

Exec procB
end

go
Create proc procb
as
Begin
Select * from #a
end

go
Exec proca

go
Drop proc proca, procb

SqlStar
Posting Yak Master

121 Posts

Posted - 2005-03-10 : 06:28:10

Vishwas,
You have to drop that global temp table explicitly.So, make clear your task.

:) While we stop to think, we often miss our opportunity :)

Arti
Starting Member

5 Posts

Posted - 2005-03-10 : 15:14:17

Use global table - flobal table remains in the system till the time your seesion is alive and could be accessed from anywhere. But don't forget to drop these table if you are modifying and running the same stored procedure again and again.

X002548
Not Just a Number

15586 Posts

Posted - 2005-03-10 : 15:19:21

quote:
Originally posted by RM

I tried this...

Create proc proca
as
Begin
create table #a (a int)
insert into #a select 1
insert into #a select 2

Exec procB
end

go
Create proc procb
as
Begin
Select * from #a
end

go
Exec proca

go
Drop proc proca, procb

His point is that it works because it's part of the same spid

Brett

8-)

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources