user1254579 user1254579 - 1 year ago 71
SQL Question

T-SQL variable show Incorrect syntax near '@tableName'

It shows an error ,when try to run this

declare @tableName VARCHAR(250)

select @tableName='['+SCHEMA_NAME(schema_id)+'].['+name+']'

FROM sys.tables
WHERE '['+SCHEMA_NAME(schema_id)+'].['+name+']'='[Management].[Table_1]'

print @tableName

TRUNCATE table @tableName

Incorrect syntax near '@tableName'.

Answer Source

That is correct. You cannot use variables to pass table names.

You can use dynamic SQL:

declare @sql nvarchar(max);
set @sql = replace('TRUNCATE table @tableName', '@tableName', @tableName);

exec sp_execute_sql @sql;

SQL statements are allowed to have parameters for constants, but not for identifiers. This is not only a SQL Server limitation, but a limitation in all (?) databases. Dynamic sql is often used for this purpose.

Recommended from our users: Dynamic Network Monitoring from WhatsUp Gold from IPSwitch. Free Download