Why am I getting a "Incorrect syntax near '@tblName' error message?

Question

Why am I getting a "Incorrect syntax near '@tblName' error message?

SSC-Dedicated

Points: 34007

August 16, 2024 at 5:04 pm

I'm working on a SQL script to delete all old tables starting with the name of "Old_". I'm testing in my test database, but it raises errors. However, if I use the PRINT statement, everything is fine. I don't get what's wrong. I'm following Microsoft's Learn page on sp_executesql, and as far as I know, I'm doing it correctly. And to test what I've done I have two lines, one for PRINT and another for DROP TABLE. The PRINT line works perfectly. The DROP TABLE line raises this error for each table that starts with "Old_":

Msg 102, Level 15, State 1, Line 1

Incorrect syntax near '@tblName'.

Here's the SQL Script:

-- A SQL Script to drop all the old tables that begin with "Old_"

-- Interate through all the "Old_" tables, drop each one
DECLARE @tableName NVARCHAR(255)    -- table name
DECLARE @tblName NVARCHAR(255)      -- table name
DECLARE @dropCommand NVARCHAR(MAX)  -- command statement
DECLARE @paramDefinition NVARCHAR(255)  -- parameter

-- Cursor to select all table names from the specified schema
DECLARE table_cursor CURSOR FOR
SELECT TABLE_NAME
FROM INFORMATION_SCHEMA.TABLES
WHERE TABLE_TYPE = 'BASE TABLE' AND TABLE_SCHEMA = 'dbo' and TABLE_NAME like 'Old_%'

OPEN table_cursor

FETCH NEXT FROM table_cursor INTO @tableName

WHILE @@FETCH_STATUS = 0
BEGIN
    SET @tableName = QUOTENAME(@tableName);
    SET @paramDefinition = N'@tblName NVARCHAR(255)';
    SET @dropCommand = N'DROP TABLE @tblName';       -- Switch between this line ...
    -- SET @dropCommand = N'PRINT @tblName';         -- ... and this line
    EXEC sp_executesql @dropCommand, @paramDefinition, @tblName = @tableName;

    FETCH NEXT FROM table_cursor INTO @tableName;
END

CLOSE table_cursor
DEALLOCATE table_cursor

The only thing I can think that might be happening is the "DROP TABLE @tblName" line may NOT be substituting the value of @tblName into that string. But that is exactly what I thought SP_EXECUTESQL did!

Kindest Regards, Rod Connect with me on LinkedIn.

August 17, 2024 at 6:48 am

This was removed by the editor as SPAM

Viewing 7 posts - 1 through 6 (of 6 total)

You must be logged in to reply to this topic. Login to reply

ScottPletcher SSC Guru Points: 101096 More actions · Answer 1

You can't use a variable in a DROP TABLE statement in SQL.

Change it to:

SET @dropCommand = N'DROP TABLE dbo.' + @tableName

SQL DBA,SQL Server MVP(07, 08, 09) "It's a dog-eat-dog world, and I'm wearing Milk-Bone underwear." "Norm", on "Cheers". Also from "Cheers", from "Carla": "You need to know 3 things about Tortelli men: Tortelli men draw women like flies; Tortelli men treat women like flies; Tortelli men's brains are in their flies".

Rod at work SSC-Dedicated Points: 34007 More actions · Answer 2

Thank you, Scott. That took care of the error message. I ran the script, it said it completed successfully. But the "Old_" tables are all still there.

Is this a permissions issue? Maybe I don't have the necessary permissions to drop a table?

Kindest Regards, Rod Connect with me on LinkedIn.

frederico_fonseca SSCoach Points: 16154 More actions · Answer 3

did you try this?

WHILE @@FETCH_STATUS = 0
BEGIN
    SET @tableName = QUOTENAME(@tableName);
    -- SET @paramDefinition = N'@tblName NVARCHAR(255)'; -- not needed for this
    SET @dropCommand = N'DROP TABLE dbo.' + @tableName;       -- Switch between this line ...
    -- SET @dropCommand = N'PRINT @tableName';         -- ... and this line
    EXEC sp_executesql @dropCommand;

    FETCH NEXT FROM table_cursor INTO @tableName;
END

This reply was modified 3 months, 1 week ago by frederico_fonseca.

ScottPletcher SSC Guru Points: 101096 More actions · Answer 4

Be sure you set and are using the correct variable. In your original code, you use "@tblName" but you never set the value of that variable. That's why I used "@tableName"

SQL DBA,SQL Server MVP(07, 08, 09) "It's a dog-eat-dog world, and I'm wearing Milk-Bone underwear." "Norm", on "Cheers". Also from "Cheers", from "Carla": "You need to know 3 things about Tortelli men: Tortelli men draw women like flies; Tortelli men treat women like flies; Tortelli men's brains are in their flies".

Jeff Moden SSC Guru Points: 1004324 More actions · Answer 5

Also, put the variable name in a QUOTENAME() function... just to be safe two ways.

--Jeff Moden

RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

Change is inevitable... Change for the better is not.

Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally)