ayilmaz ayilmaz - 1 year ago 64
SQL Question

How to create sp_executesql to drop tables dynamicaly

For some reasons, I am trying to create a dynamic script to drop tables that I created before. I couldnt do the syntax right and I need help for this matter.

When I run my script, it gives the error "Procedure expects parameter '@statement' of type 'ntext/nchar/nvarchar'."

and this is my script. It has an error in sp_executesql statement, I guess. How can I fix this?

DECLARE @tmpTableName VARCHAR(max);
SET @tmpTableName = '##gmAAA_COLLATION';

SET @sql = 'DROP TABLE @tmpTableName';

EXEC sp_executesql @sql, N'@tmpTableName NVARCHAR(max)', @tmpTableName;

Answer Source

You cannot do this with static SQL, i.e. a table name can never be a parameter in SQL statements like these. This is also true for column names, schema names etc.

If you want to do this using sp_executesql, you can build the SQL dynamically as follows:

SET @sql = 'DROP TABLE '+QUOTENAME(@tmpTableName);

EXEC sp_executesql @sql;

PS: The @stmt parameter of the sp_executesql procedure needs to be of type NVARCHAR(...).

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