August 24, 2005 at 1:28 am
I have a sproc, similar to that listed below, that has a input parameter of a table name. I want to do a SQL on this table and saved the output to a #Temp table. However, the #Temp table disappears after the "exec".
I don't want to create a ##Temp table becuase of potential conflict if the sproc is run by 2 connections at the same time. I prefer not to create the #Temp table and "insert" into it. What is the simplest way to create this #Temp table?
create procedure TestInputTable (@InputTable varchar(100))
as begin
exec ('select * into #TempTable from ' + @InputTable)
-- the follwoing 2 lines fail because #TempTable not exists
select * from #TempTable where Id < 100
select * from #TempTable where Id >= 1000
end
August 24, 2005 at 2:16 am
How about putting the whole statement in one string and then executing that? Something like this:
create procedure TestInputTable (@InputTable varchar(100))
as begin
declare @strSQL varchar(8000)
set @strSQL= 'select * into #TempTable from ' + @InputTable
set @strSQL= @strSQL+ ' select * from #TempTable where Id < 100 select * from #TempTable where Id >= 1000'
exec (@strSQL)
end
The absence of evidence is not evidence of absence
- Martin Rees
The absence of consumable DDL, sample data and desired results is, however, evidence of the absence of my response
- Phil Parkin
August 25, 2005 at 7:27 am
Why do you need to do that???
The Curse and Blessings of Dynamic SQL
Viewing 3 posts - 1 through 2 (of 2 total)
You must be logged in to reply to this topic. Login to reply