demo demo - 4 months ago 17
SQL Question

DROP PROCEDURE throws syntax error

I have a

#temp
table where I have names of stored procedures.

DECLARE @object VARCHAR(200)
SET @object = (SELECT Top 1 Te.[this Object...] From #Temp Te)

IF OBJECT_ID(@object) IS NOT NULL
DROP PROCEDURE @object


But on last step I get error


Incorrect syntax near '@object'.


Is this because
@object
is of type
VARCHAR
or what ?

This is for SQL Server 2012

Answer

you can't do it like that. You need to use dynamic sql. Something like :

DECLARE @object NVARCHAR(200)
DECLARE @sql NVARCHAR(max)
SET @object = (SELECT Top 1 Te.[this Object...] From #Temp Te)
IF OBJECT_ID(@object) IS NOT NULL
BEGIN
    SET @sql = 'DROP PROCEDURE ' + @object
    sp_executesql @sql
END
Comments