asked on

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

Lowfatspread

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....

jxharding

ASKER

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

Lowfatspread

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)

Lowfatspread

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

SOLUTION

Scott Pletcher

membership

This solution is only available to members.

To access this solution, you must be a member of Experts Exchange.

Start Free Trial

jxharding

ASKER

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

ASKER CERTIFIED SOLUTION

Lowfatspread

membership

This solution is only available to members.

To access this solution, you must be a member of Experts Exchange.

Start Free Trial

Scott Pletcher

Change my last statement to:

EXEC('SELECT * FROM ##t1')

which makes it dynamic SQL also :-) .