Create a Dynamic Temp 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)

Create a Dynamic Temp Table

Author

Topic

AskSQLTeam
Ask SQLTeam Question

0 Posts

Posted - 2003-10-17 : 07:25:15

wayne writes "My code is below. I want to create this temp table. when I run it, the select statement says "Invalid object name '#tempPromos'.", however, when I cut the printed string, and paste it into Query Analyzer, it works fine... How do I fix it?

DECLARE @Group varchar(200),
@SQL varchar(1000);

DECLARE TheGroups CURSOR FOR
SELECT ReportGroupID
FROM dbo.ReportGroup
OPEN TheGroups;
SET @SQL ='CREATE TABLE #tempPromos (TTID int, Descr varchar (200)'
FETCH NEXT FROM TheGroups INTO @Group
WHILE @@FETCH_STATUS = 0
BEGIN
SET @SQL =@SQL+', L'+@Group+' money'
FETCH NEXT FROM TheGroups INTO @Group
END
SET @SQL = @SQL+')'
PRINT @SQL
EXECUTE (@SQL);
CLOSE TheGroups;
DEALLOCATE TheGroups;
SELECT * FROM #tempPromos
drop table #tempPromos"

drymchaser
Aged Yak Warrior

552 Posts

Posted - 2003-10-17 : 08:39:53

Can you move the EXECUTE(@SQL) outside of the cursor CLOSE and DEALLOCATE?

Stoad
Freaky Yak Linguist

1983 Posts

Posted - 2003-10-17 : 08:44:48

You're trying to select from a temp table which was
created in other (yes!) connection space than that your
select statement is issued in.
Maybe better to use global temp tables (##temp)?

Lavos
Posting Yak Master

200 Posts

Posted - 2003-10-17 : 10:40:55

Ahh, but then that global table can be hit by any process running the same procedure. Not a good alternative. Of course, he could incorporate the current process id into the temp table's name and it would work then. hmmmm.



DECLARE @Group varchar(200)
DECLARE @TableName varchar(128)

DECLARE @SQL varchar(1000);

SET @TableName = '##tempPromos'+CONVERT(varchar, @@SPID)

SET @SQL ='CREATE TABLE '+@TableName+' (TTID int, Descr varchar (200)'

SELECT @SQL = @SQL+', L'+ReportGroupID+' money'
FROM dbo.ReportGroup


SET @SQL = @SQL+')'

PRINT @SQL

EXECUTE (@SQL);

SET @SQL= 'SELECT * FROM '+@TableName+'
	drop table '+@TableName

EXEC(@Sql)

Of course, now everything has to use dynamic SQL to access it.

----------------------
"O Theos mou! Echo ten labrida en te mou kephale!"

"Where theres a will, theres a kludge." - Ken Henderson

nr
SQLTeam MVY

12543 Posts

Posted - 2003-10-17 : 11:23:37

Why a cursor (ever)?

DECLARE @Group varchar(200),
@SQL varchar(1000)
declare @SQL varchar(2000)
select @sql = 'CREATE TABLE #tempPromos (TTID int, Descr varchar (200)'
select @sql = @sql + ',L' + convert(varchar(200),ReportGroupID) + ' money'
from dbo.ReportGroup
select @sql = @sql + ') select * from #tempPromos'
exec (@sql)

Of course this will select from an empty table but ....

==========================================
Cursors are useful if you don't know sql.
DTS can be used in a similar way.
Beer is not cold and it isn't fizzy.

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources