CODEWITHSUNDEEP
sqlsql-serverstored-proceduressql-server-2012
demo
demo

Reputation: 6235

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

Upvotes: 2

Views: 536

Answers (3)

librata
librata

Reputation: 150

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

Upvotes: 4

Alex
Alex

Reputation: 97

DROP PROCEDURE needs an object not an varchar

try:

EXECUTE ('DROP PROCEDURE ' + @object)

Upvotes: 0

2b77bee6-5445-4c77-b1eb-4df3e5
2b77bee6-5445-4c77-b1eb-4df3e5

Reputation: 967

Have a look here:

https://dba.stackexchange.com/questions/47186/how-can-i-drop-a-stored-procedure-whose-name-is-in-a-variable

You need to write an sql statement and execute it seperately.

Part from that answer:

declare @sql varchar(64);

set @sql = 'drop proc ' + @stored_procedure_name;

exec(@sql);

And you can replace @stored_procedure_name with @object

Upvotes: 3

Related Questions