Take the 2-minute tour ×
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's 100% free, no registration required.

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?

share|improve this question
    
I can't help wondering what led to this question being asked. Why do you have stored procedure names in variables that need to be dropped. (This is the second question Sam has asked about procedure names in variables). –  Paul White Jul 29 '13 at 3:31
    
@PaulWhite, I have a situation where as part of an application's DB deployment script, it should execute and drop a set of stored procedures. I'm using this approach due to the way the tools I'm using work. –  Sam Jul 29 '13 at 4:03
add comment

3 Answers

up vote 6 down vote accepted

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.

share|improve this answer
add comment

Use dynamic SQL:

declare @sql varchar(64);

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

exec(@sql);
share|improve this answer
    
This doesn't look like it escapes the stored procedure name; wouldn't this break if the stored procedure name variable contained '? –  Sam Jul 29 '13 at 0:39
add comment

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
share|improve this answer
    
This doesn't look like it escapes the stored procedure name; wouldn't this break if the stored procedure name variable contained '? –  Sam Jul 29 '13 at 0:39
    
Good point @Sam. I've added QUOTENAME and a bit more. –  Kenneth Fisher Jul 29 '13 at 2:18
add comment

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

Not the answer you're looking for? Browse other questions tagged or ask your own question.