Given a variable that contains a stored procedure name:
DECLARE @stored_procedure_name sysname;
SET @stored_procedure_name = 'some_stored_procedure_name';
How can I drop the stored procedure?
Database Administrators Stack Exchange is a question and answer site for database professionals who wish to improve their database skills and learn from others in the community. It only takes a minute to sign up.
Sign up to join this communityGiven a variable that contains a stored procedure name:
DECLARE @stored_procedure_name sysname;
SET @stored_procedure_name = 'some_stored_procedure_name';
How can I drop the stored procedure?
To do this, you must use dynamic SQL, as the statement DROP PROCEDURE
cannot be parameterized.
As a bit of an aside, it's a good practice to always schema-qualify object names. You didn't specify the schema name in the question (or say that the variable contains a complete dot-separated name), so I've gone ahead and extended what you asked for to include it.
DECLARE @stored_procedure_schema sysname = NULL;
DECLARE @stored_procedure_name sysname = N'some_stored_procedure_name';
DECLARE @sql nvarchar(MAX) =
N'DROP PROCEDURE ' +
ISNULL(QUOTENAME(@stored_procedure_schema) + N'.', N'') +
QUOTENAME(@stored_procedure_name) + N';';
EXEC(@sql);
This doesn't do any checking to see if the procedure actually exists or not before it attempts to drop it, so you'll get an error if it doesn't exist. It's trivial to use sys.procedures
to check for existence, so I didn't bother with that, and you didn't ask for it either.
To preemptively answer your comment on the other answers, QUOTENAME
will correctly escape any object name such that it ends up surrounded by square brackets. Specifically, yes, this does handle single quotes (and square brackets) within the procedure name itself.
Use dynamic SQL:
declare @sql varchar(64);
set @sql = 'drop proc ' + @stored_procedure_name;
exec(@sql);
'
?
– Sam
Jul 29 '13 at 0:39
Dynamic SQL
EDIT:
Simple version with QUOTENAME
added
DECLARE @stored_procedure_name sysname;
SET @stored_procedure_name = 'some_stored_procedure_name';
DECLARE @sql nvarchar(max);
SET @sql = 'DROP PROCEDURE ' + QUOTENAME(@stored_procedure_name)
EXEC sp_executesql @sql
Slightly more complex version with schema name variable
, QUOTENAME
and test for the existence of the stored procedure.
DECLARE @stored_procedure_name sysname;
DECLARE @stored_procedure_schema sysname;
SET @stored_procedure_name = 'mytest';
IF ISNULL(@stored_procedure_schema,'') = ''
SET @stored_procedure_schema = 'DBO';
DECLARE @object_name sysname;
SET @object_name = QUOTENAME(@stored_procedure_schema) + '.' +
QUOTENAME(@stored_procedure_name);
IF OBJECT_ID(@object_name) <> 0
BEGIN
DECLARE @sql nvarchar(max);
SET @sql = 'DROP PROCEDURE ' + @object_name;
EXEC sp_executesql @sql;
END
'
?
– Sam
Jul 29 '13 at 0:39