Deleting Stored Procedures
You can delete a stored procedure when you no longer need it. If a stored procedure that has been deleted is called by another stored procedure, Microsoft SQL Server displays an error message when the calling procedure is executed. However, if a new stored procedure of the same name and the same parameters is defined to replace the one that was deleted, other procedures that reference it will still execute successfully. For example, if stored procedure proc1 references stored procedure proc2, and proc2 is deleted and a different stored procedure called proc2 is created, proc1 now references the new stored procedure. proc1 does not have to be recreated.
To delete a stored procedure
To delete an extended stored procedure