Using Dynamic SQL to build temp table...doesn't work?

Question

Using Dynamic SQL to build temp table...doesn't work?

Marcus Farrugia

SSCarpal Tunnel

Points: 4718
More actions
September 30, 2013 at 10:24 am

#278616

I am trying to pass a column name as a parameter to build a #temp table combining data from two tables. When I exec the dynamic query it seems to build the temp table because I am receiving a message saying 2048 rows affected; however, I can't select from it, I am receiving an error saying that #TempLevels is an invalid object. Is this possible to do? What am I missing? Thank you in advance!
declare @ColumnLevel as varchar(10);
declare @ReportLevel as varchar(10);
Set @ReportLevel = '2'
set @ColumnLevel =
case
when @ReportLevel = '2' then 'level2'
when @ReportLevel = '3' then 'level3'
when @ReportLevel = '4' then 'level4'
end
print @ColumnLevel
declare @sql as varchar(1000)
set @sql =
'select * into #TempLevels from tblLevels as a join tblReports as b on a.'+ColumnLevel+' = b.ReportLevel'
exec(@sql)
select * from #TempLevels
drop table #TempLevels

Viewing 6 posts - 1 through 5 (of 5 total)

You must be logged in to reply to this topic. Login to reply

Gail Shaw SSC Guru Points: 1004504 More actions · Answer 1

A temp table is dropped as soon as the scope in which it was created ends, that scope is the dynamic SQL, so it's created, populated and then dropped at the end of the dynamic SQL

You either need to CREATE TABLE #... outside the dynamic SQL or do all processing in a single piece of dynamic SQL. The first option is definitely preferred.

Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability

We walk in the dark places no others will enter
We stand on the bridge and no one may pass

Marcus Farrugia SSCarpal Tunnel Points: 4718 More actions · Answer 2

Thank you for the explanation Gila 🙂

Thant really helps and clears my confusion, I'll use your suggestion.

Luis Cazares SSC Guru Points: 183698 More actions · Answer 3

You could also avoid using dynamic SQL, even if it might reduce performance a bit.

You could try this.

DECLARE @ReportLevel AS VARCHAR(10);

SELECT *

INTO #TempLevels

FROM tblLevels AS a

INNER JOIN tblReports AS b ON CASE

WHEN @ReportLevel = '2'

THEN a.level2

WHEN @ReportLevel = '3'

THEN a.level3

WHEN @ReportLevel = '4'

THEN a.level4

END = b.ReportLevel

Luis C.
General Disclaimer:
Are you seriously taking the advice and code from someone from the internet without testing it? Do you at least understand it? Or can it easily kill your server?

How to post data/code on a forum to get the best help: Option 1 / Option 2

Alan Burstein SSC Guru Points: 61136 More actions · Answer 4

What Louis posted is the way to go; no DSQL needed for what you are doing. That said, to accomplish this with a temp table you would need to use a global temp table (e.g. temp table that begins with "##") instead of a local temp table.

Below is a modified version of your code (since I don't have access to your tblLevels and tblReports tables) that would work.

declare @ColumnLevel as varchar(10);

declare @ReportLevel as varchar(10);

Set @ReportLevel = '2'

set @ColumnLevel =

case

when @ReportLevel = '2' then 'level2'

when @ReportLevel = '3' then 'level3'

when @ReportLevel = '4' then 'level4'

end

print @ColumnLevel

declare @sql as varchar(1000)

set @sql =

'select *, '''+@ColumnLevel+''' AS xxx into ##TempLevels from sys.databases'

exec(@sql)

select * from ##TempLevels

drop table ##TempLevels

"I cant stress enough the importance of switching from a sequential files mindset to set-based thinking. After you make the switch, you can spend your time tuning and optimizing your queries instead of maintaining lengthy, poor-performing code."

-- Itzik Ben-Gan 2001

ScottPletcher SSC Guru Points: 100950 More actions · Answer 5

Be aware that a global temp table means the code must be single-threaded, as simultaneous executions of the code would stomp on each other.

SQL DBA,SQL Server MVP(07, 08, 09) A socialist is someone who will give you the shirt off *someone else's* back.