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 @sql VARCHAR(MAX);
DECLARE @tmpTableName VARCHAR(max);
SET @tmpTableName = '##gmAAA_COLLATION';
SET @sql = 'DROP TABLE @tmpTableName';
EXEC sp_executesql @sql, N'@tmpTableName NVARCHAR(max)', @tmpTableName;
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(...).