SQL Server Books Online: deleting stored procedures

server| stored procedures can be deleted when the stored procedure is no longer needed. If another stored procedure calls a deleted stored procedure, Microsoft®sql server™2000 displays an error message when the calling procedure is executed. However, if a new stored procedure with the same name and parameter is defined to replace the deleted stored procedure, the other processes that refer to the process can still execute smoothly. For example, if a stored procedure Proc1 reference to a stored procedure proc2, and PROC2 is deleted, but another stored procedure named Proc2 is created, Proc1 will now refer to the new stored procedure and Proc1 do not have to recompile.
After the stored procedures are grouped, you cannot delete individual stored procedures within the group. Deleting a stored procedure deletes all stored procedures within the same group.
DROP PROCEDURE
Deletes one or more stored procedures or procedure groups from the current database.
Grammar
DROP PROCEDURE {PROCEDURE} [,... N]
Parameters
Procedure

is the name of the stored procedure or stored procedure group to be deleted. The procedure name must conform to the rules for identifiers. For more information, see Using Identifiers. You can choose whether to specify the procedure owner name, but you cannot specify the server name and database name.

N

is a placeholder that indicates that multiple procedures can be specified.
Comments
To see a list of procedure names, use sp_help. To display the procedure definition (stored in the syscomments system table), use sp_helptext. When you drop a stored procedure, information about the procedure is removed from the sysobjects and syscomments system tables.

You cannot drop individual procedures within a group, you must drop the entire process group.

Regardless of whether a user-defined system procedure (prefixed by sp_) is the current database, it is removed from the master database. If a system procedure is not found in the current database, Microsoft®sql Server™ attempts to remove it from the master database.
Permissions
By default, the DROP PROCEDURE permission is granted to the procedure owner, and the permission is not transferable. However, db_owner and db_ddladmin fixed database role members and members of the sysadmin fixed server role can drop any object by specifying the owner within the DROP PROCEDURE.
Example
The following example deletes the byroyalty stored procedure (within the current database).

DROP PROCEDURE Byroyaltygo from: http://goaler.xicp.net/ShowLog.asp?ID=518