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?
 
LowfatspreadConnect With a Mentor Commented:
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
 
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
Get your problem seen by more experts

Be seen. Boost your question’s priority for more expert views and faster solutions

 
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
 
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 PletcherConnect With a Mentor Senior 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
 
Scott PletcherSenior DBACommented:
Change my last statement to:

EXEC('SELECT * FROM ##t1')

which makes it dynamic SQL also :-) .
0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.