Give the DROP PROCEDURE parameter

Question

I am using SqlServer for the first time, and each of our scripts for creating a procedure has a code block, as shown below, to delete a 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 the cutting and paste this template code into each file, I would like to put this code in my own stored procedure so that the scripts look like this instead:

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

My attempt at a solution:

 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 get the following error:

Msg 102, Level 15, State 1, DeleteProcedure Procedure, Line 10 Invalid syntax next to '@procedureName'.

Any ideas how to do what I want?

Answer 1

Full answer:

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

The one set by Andrew will only work if the default database for your login is configured to the correct database. When using dynamic sql you get a new database context. Therefore, if you do not have a default dataset, you will run the wizard command.

Answer 2

It should be noted that in the DropIfRequired procedure, you defined the procedure name as follows:

 CREATE PROCEDURE DropIfRequired ( @procedureName varchar ) 

You need to determine the length of the varchar parameter, otherwise SQL will take the length of one character. Instead, do something like the following (1000 should be more than enough for most procedure names)

 CREATE PROCEDURE DropIfRequired ( @procedureName varchar(1000) ) 

Answer 3

its missing quotes, try adding them using the exec statement.

 EXEC( 'DROP PROCEDURE ''' + @procName + '''') ( all single quotes)