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?
To do this, you must use dynamic SQL, as the statement
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.
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
To preemptively answer your comment on the other answers,
Use dynamic SQL:
Simple version with
Slightly more complex version with