possible to use local temp table OR table variable in dynamic SQL? -

Is it possible to use local temp table OR table variable in dynamic SQL?

Attached an attempt to use both of them, but resulting in
1. Invalid object name '#TEMP'. (for temp table)
2. Must declare the table variable "@TEMP". (for table variable)

-- Variable that will contain the name of the table
   declare @mytable varchar(30)
   -- Creates a temp table name
   select @mytable ='#TEMP'+ CONVERT(char(12), GETDATE(), 14)
   print @mytable

   -- Table cannot be created with the character  ":"  in it
   -- The following while loop strips off the colon
   declare  @pos int
   select   @pos = charindex(':',@mytable)

   while @pos > 0
      begin
           select @mytable = substring(@mytable,1,@pos - 1)   +
                             substring(@mytable,@pos + 1,30-@pos )
           select @pos = charindex(':',@mytable)
      end
   print 'Name without colon is :'
   print @mytable

   -- Create the temporary table
   execute ('create table '+ @mytable +
             '(col1 int)' )

   -- Insert two rows in the table
   execute ('insert into ' + @mytable +
                 ' values(1)')

   execute ('insert into ' + @mytable +
                 ' values(2)')

   -- Select from the temporary table
   execute ('select col1 from ' + @mytable )
   -- Drop the temporary table
   execute ('drop table ' + @mytable)

Open in new window

-- Variable that will contain the name of the table
   declare @mytable varchar(30)
   -- Creates a temp table name
   select @mytable ='@TEMP'+ CONVERT(char(12), GETDATE(), 14)
   print @mytable

   -- Table cannot be created with the character  ":"  in it
   -- The following while loop strips off the colon
   declare  @pos int
   select   @pos = charindex(':',@mytable)

   while @pos > 0
      begin
           select @mytable = substring(@mytable,1,@pos - 1)   +
                             substring(@mytable,@pos + 1,30-@pos )
           select @pos = charindex(':',@mytable)
      end
   print 'Name without colon is :'
   print @mytable

   -- Create the temporary table
   execute ('Declare '+ @mytable + ' Table (col1 int)' )

   -- Insert two rows in the table
   execute ('insert into ' + @mytable +
                 ' values(1)')

   execute ('insert into ' + @mytable +
                 ' values(2)')

   -- Select from the temporary table
   execute ('select col1 from ' + @mytable )
   -- Drop the temporary table
   execute ('drop table ' + @mytable)

Open in new window

jxhardingAsked:
Who is Participating?

[Product update] Infrastructure Analysis Tool is now available with Business Accounts.Learn More

x
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

LowfatspreadCommented:
yes its possible to use a temp table within a batch of dynamic sql....

please explain what you are attempting to do....


ps to remove the colon   just do   replace(@str.':','')  rather than the loop....


0
jxhardingAuthor Commented:
hi @Lowfatspread
thanks for the tip on the colon

if i use the code as it was from microsoft
(code attached, i removed the # from the table name)

then it works, i can insert the the values in the database, but as soon as i make it a #temporary table or a @table variable then everything breaks.

I have to do it for a large program but im trying to sort out the basic idea first and get around to how to do it and thus included this code to set up a basic scenario, it is thus just inserting two records into a #temp table

thank you
-- Variable that will contain the name of the table
   declare @mytable varchar(30)
   -- Creates a temp table name
   select @mytable ='TEMP'+ CONVERT(char(12), GETDATE(), 14)
   print @mytable

   -- Table cannot be created with the character  ":"  in it
   -- The following while loop strips off the colon
   declare  @pos int
   select   @pos = charindex(':',@mytable)

   while @pos > 0
      begin
           select @mytable = substring(@mytable,1,@pos - 1)   +
                             substring(@mytable,@pos + 1,30-@pos )
           select @pos = charindex(':',@mytable)
      end
   print 'Name without colon is :'
   print @mytable

   -- Create the temporary table
   execute ('create table '+ @mytable +
             '(col1 int)' )

   -- Insert two rows in the table
   execute ('insert into ' + @mytable +
                 ' values(1)')

   execute ('insert into ' + @mytable +
                 ' values(2)')

   -- Select from the temporary table
   execute ('select col1 from ' + @mytable )
   -- Drop the temporary table
   execute ('drop table ' + @mytable)

Open in new window

0
LowfatspreadCommented:
the defined table only lasts for the duration of the batch "Exec(...)" statement...

-- Variable that will contain the name of the table
   declare @mytable varchar(30)
   -- Creates a temp table name
   select @mytable ='TEMP'+ CONVERT(char(12), GETDATE(), 14)
   print @mytable

   -- Table cannot be created with the character  ":"  in it
   -- The following while loop strips off the colon
   declare  @pos int
   select   @pos = charindex(':',@mytable)

   while @pos > 0
      begin
           select @mytable = substring(@mytable,1,@pos - 1)   +
                             substring(@mytable,@pos + 1,30-@pos )
           select @pos = charindex(':',@mytable)
      end
   print 'Name without colon is :'
   print @mytable

   -- Create the temporary table
   execute ('create table '+ @mytable +
             '(col1 int)'
   +'insert into ' + @mytable +
                 ' values(1)'

  +'insert into ' + @mytable +
                 ' values(2)'

  +'select col1 from ' + @mytable
+'drop table ' + @mytable)

0
Big Business Goals? Which KPIs Will Help You

The most successful MSPs rely on metrics – known as key performance indicators (KPIs) – for making informed decisions that help their businesses thrive, rather than just survive. This eBook provides an overview of the most important KPIs used by top MSPs.

LowfatspreadCommented:
what do you need to do...

dynamic sql should be your last choice for any task...

it is inherently risky. insecure, and difficult to performance tune/manage.maintain


0
Scott PletcherSenior DBACommented:
A global temp table should work, atlho unfortunately MS now automatically deletes those as well.

EXEC('CREATE TABLE ##t1 (c1 int)')
EXEC('INSERT INTO ##t1 values(1)')

SELECT * FROM ##t1


>> table variable in dynamic SQL <<

No, never.  That table variable will never survive beyond a single EXEC() statement.
0
jxhardingAuthor Commented:
i please need help on following:
1. create a temp table  - via dynamic sql
2. insert values into the table (1 ,2 in the example) - via dynamic sql
3. select * from table - via dynamic sql

the code attached in the first post, was my 2 attempts to try it with either #temp or @temp
but it does not look like it is possible

0
LowfatspreadCommented:
this should work...

execute ('create table #mytable '+
             '(col1 int)'
   +' '
   +'insert into #mytable '+
                 ' values(1)'
  +' '
  +'insert into #mytable '+
                 ' values(2)'
  +' '
  +'select col1 from #mytable'
 +' '
+'drop table  + #mytable')


what (and why)  are you trying to do
0

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
Scott PletcherSenior DBACommented:
Change my last statement to:

EXEC('SELECT * FROM ##t1')

which makes it dynamic SQL also :-) .
0
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Microsoft SQL Server 2005

From novice to tech pro — start learning today.