Give DROP PROCEDURE a parameter

Question

I'm using SqlServer for the first time, and in every single one of our create procedure scripts there is a block of code like below to remove the procedure if it already exists:

IF EXISTS (SELECT *
           FROM information_schema.routines
           WHERE routine_name = 'SomeProcedureName'
           AND routine_type = 'PROCEDURE'

BEGIN
    DROP PROCEDURE SomeProcedureName
END
//then the procedure definition

To stop cutting and pasting this boilerplate code in every file I would like to put this code in its own stored procedure so that instead the scripts would look like this:

DropIfRequired('SomeProcedureName')
//then the procedure definition

My attempt at a solution is:

CREATE PROCEDURE DropIfRequired
(
    @procedureName varchar
)
AS
IF EXISTS (SELECT * FROM information_schema.routines 
           WHERE routine_name = @procedureName 
           AND routine_type = 'PROCEDURE') 
BEGIN
    DROP PROCEDURE @procedureName
END

But I then get the following error:

Msg 102, Level 15, State 1, Procedure DeleteProcedure, Line 10 Incorrect syntax near '@procedureName'.

Any ideas how to do what I want?

Answer 1

The full answer is:

DECLARE @SQL VARCHAR(8000)
SELECT @SQL = 'USE ' + DB_NAME() + CHAR(10)
SET @SQL = @SQL + 'DROP PROCEDURE ' + @procName
--PRINT @SQL
EXEC(@SQL)

The one given by Andrew will only work if the default database for your login is set to the database you want. When using dynamic sql you get a new database context. So if you do not have a default database set you will execute the command from master.