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

Question

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

Matt L · Accepted Answer · 2018-12-04 22:47:45Z

4

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

TSQL sp_executesql

edited Dec 4, 2018 at 22:47

Matt L

7699 silver badges14 bronze badges

answered Jun 12, 2016 at 14:35

Gordon Linoff

1.3m62 gold badges706 silver badges857 bronze badges

Sign up to request clarification or add additional context in comments.

1 Comment

Gordon Linoff Over a year ago

@user1254579 . . . That is how SQL is defined. In the case of SQL Server, there is a conflict with a table variable and a table name. To be honest, if the variable were set at compile time, I don't see an issue with it -- but no database supports this.

Collectives™ on Stack Overflow

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

1 Answer 1

1 Comment

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

1 Comment

Your Answer

Sign up or log in

Post as a guest

Related