Reg: Exec statement and temporary tables

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

Transact-SQL (2000)

Reg: Exec statement and temporary tables

Author

Topic

AskSQLTeam
Ask SQLTeam Question

0 Posts

Posted - 2007-02-06 : 08:59:12

Praveen writes "Hi,
Here is a dumb question -

alter procedure dbo.test31 with recompile as

begin
declare @tab varchar(10)
declare @sql varchar(100)
declare @sql1 varchar(100)
set @tab = '#tt1'
select @sql = 'create table '+@tab+' (empno integer)'
Set @sql1 = ' insert into '+@tab+' values(13)'
Exec (@sql)
Exec (@sql1)
end
GO

When i try to run this stored proc i get the followin error.
Server: Msg 208, Level 16, State 1, Procedure test31, Line 37
Invalid object name '#tt1'.
Any explanations............ :)"

khtan
In (Som, Ni, Yak)

17689 Posts

Posted - 2007-02-06 : 09:06:32

temp table only exists within the context. In this case, it is created in exec(@sql), but when exec() terminated, it is also drop.

harsh_athalye
Master Smack Fu Yak Hacker

5581 Posts

Posted - 2007-02-06 : 09:39:09

You can either combine both CREATE TABLE and INSERT statements inside single EXEC call or you can create global temp table to solve this problem or you can create temp table outside EXEC.

Harsh Athalye
India.
"The IMPOSSIBLE is often UNTRIED"

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources