How to create sp_executesql to drop tables dynamicaly

Question

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;

how did you create that global temp table in the first place ? Also dynamcially ? — Squirrel, Nov 16 '16 at 07:22

score 3 · Accepted Answer · answered Nov 16 '16 at 07:20

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(...).

Chanukya · Answer 2 · 2016-11-16T07:51:44.600

2

SET @sql = 'DROP TABLE '+@tmpTableName;

EXEC sp_executesql @sql;

edited Nov 16 '16 at 07:51

answered Nov 16 '16 at 07:21

Chanukya

5,833
1
22
36

miss a `+` to concatenate the @tblTableName to the DROP TABLE command – Squirrel Nov 16 '16 at 07:30

score 1 · Answer 3 · answered Nov 16 '16 at 07:20

1

The sp_executesql requires nvarchar for the @stmt and @params parameters

so change the data Types of variables form varchar to be nvarchar as following

DECLARE @sql NVARCHAR(MAX);
DECLARE @tmpTableName VARCHAR(max);

answered Nov 16 '16 at 07:20

ahmed abdelqader

3,409
17
36

I already did that but now it says "Incorrect syntax near '@tmpTableName'." – Arif YILMAZ Nov 16 '16 at 07:20

score 0 · Answer 4 · edited Nov 16 '16 at 07:24

0

Try the following query:-

    SET @sql = 'DROP TABLE @tmpTableName'; EXEC (@sql)

OR

DECLARE @sql NVARCHAR(MAX);
DECLARE @tmpTableName NVARCHAR(max)
SET @tmpTableName = '##gmAAA_COLLATION';


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

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

edited Nov 16 '16 at 07:24

Chanukya

5,833
1
22
36

answered Nov 16 '16 at 07:08

Mansoor

4,061
1
17
27

I need to use sp_executesql because I might use INSERT statemtn instead of DROP – Arif YILMAZ Nov 16 '16 at 07:13
@Mansoor what is the purpose of `@tmpTableName` inside the Dynamic Query ? It is not used at all – Squirrel Nov 16 '16 at 07:28

How to create sp_executesql to drop tables dynamicaly

4 Answers4