Table Identifier Variable

[zdoj]

I am dynamically creating tables in stored procedures, and would like to use variables for the table identifiers. Something like:

CREATE TABLE @TableName (
-- ...

But I always get a syntax error "near '@TableName'". I am currently concatenating an entire SQL string and running it with sp_executesql, but I suspect it bypasses much stored proc optimisation, which is what I want (the creation strings are fairly long).

Another developer told me it works for him, but I can't seem to get it to work. Any ideas/workarounds? I am running MSDE 2000, and using both ODBC and ADO.Net.

TIA

 

[SriSamp]

If you can send the DDL statement, I can give a more correction solution, but are you concatenating the table variable with the CREATE TABLE statement?

SET @statement = 'CREATE TABLE ' + @tcTableName + ' ....'

HTH
SriSamp

 

[Vinodk]

When using table variables use the declare directive:

declare @myTable table (col1 int ...

HTH,
Vinod Kumar

 

[tlbroadbent]

You cannot use a variable for object or column names in SQL statemetns. Dynamic execution is the only way to do what you want to do. sp_ExecuteSQL is the best way to optimize dynamic SQL executions. If you want to get the best answer for your question read faq183-874 and faq183-3179.
Terry L. Broadbent - DBA
SQL Server Page:

http://tlbroadbent.home.attbi.com/sql/sql_articles.htm