Dynamic SQL statement to Create Table Fails

[YaBabyYa]

Hello,

I am using some code to dynamically create a table. The columns of the table are determined at run time.

The code is creating the proper TSQL statement and when I execute the statement independently it creates the table. However, in the code body after Executing the TSQL when I try to reference the table, it gives an "Invalid Object Name #Input_Word_Chars " error, where #Input_Word_Chars is the table that is created in the Dynamic T-SQL statment

Code snippet is as follows:
************************************************************

set @Len =0
While(@Len < @Unique_W_Len)
BEGIN
set @curr_char = substring(@unique_word_string , @Len+1, 1)
if(@curr_char != ' ')
begin
set @input_table_string = @input_table_string + ' '
+ @curr_char + ' float, '
END

Set @Len = @Len + 1
END

set @input_table_string = @input_table_string + ' word varchar(80) )'
Print @input_table_string
Exec (@input_table_string)

Insert Into #Input_word_Chars (word) values (@input_word)

************************************************************



Error occurs at the last line " Insert Into
#Input_word_chars ... "



Exec(@input_table_string) creates the table

The print output for @input_table_string is:

*********************************************************

Create Table #Input_word_Chars ( t float, e float, s float, word varchar(80) )

 

[SQLDenis]

you are out of scope when creatin the table in dynamic sql and returning to 'real' sql

use 2 #

##Input_word_chars

but now the problem will be that this table is not unique anymore, so if 2 people run the same code one of them will get an error or worse will get the result from each other

Denis The SQL Menace
--------------------
SQL Server Code,Tips and Tricks, Performance Tuning
SQLBlog.com, Google Interview Questions

 

[ESquared]

Create the table #Input_Word_Chars earlier in the script with one column. In your dynamic SQL, drop the table and recreate it, or alter it to suit. Now you won't get any error.

Why are you using dynamic SQL anyway, though? Justify why you're using it. Best practice is to avoid dynamic SQL wherever possible.

[COLOR=black #d0d0d0]When I walk, I sometimes bump into things. I am closing my eyes so that the room will be empty.[/color]

 

[YaBabyYa]

Hello E-Squared,

I am using the dynamic SQL for very specific reasons.

The reason is that the table columns are determined based on the input coming into the stored procedure.

As a result I have to create the table using an Exec call and substituing the column names when the input arrives.

Originally, I didn't go with a global table name, but was forced to go with that approach. If I don't go with the global table names the query is going out of scope. As indicated by Denis in his earlier post.

As a result I define and then drop these global tables in the query. I have taken note of the best practises and need help with the current methodology as this is the design pattern I have chosen.

 

[YaBabyYa]

Once you create the Table name dynamically all the manipulations on that table have to be made through Exec calls.

The stored proc does not have a definition of the table, so the only place it can look is in the globals.

Anyway..I have a recent post related to this issue. .. if you know the answer please reply.

Thanks.

 

[ESquared]

You do NOT have to use a global temporary table.

I was sure that I had posted this, not sure where it went.

Create the temp table ahead of the dynamic SQL with the columns it will always have (or a dummy column if necessary), and alter it in the dynamic SQL (or even drop and recreate it) to add the columns you want. This will avoid schema check errors.

Voila. Problem solved.

[COLOR=black #d0d0d0]When I walk, I sometimes bump into things. I am closing my eyes so that the room will be empty.[/color]

 

[YaBabyYa]

E-squared is the man .. yes I did what he said and it works.

Nice.