SQL Server books online: delete stored procedures

Delete stored procedure

You can delete stored procedures when they are no longer needed. If another stored procedure calls a deleted stored procedure, Microsoft SQL Server 2000 displays an error message when executing the call procedure. However, if a new stored procedure with the same name and parameters is defined to replace the deleted stored procedure, other processes that reference the procedure can still be executed smoothly. For example, if the Stored Procedure proc1 references the Stored Procedure proc2 and proc2 is deleted but another Stored Procedure named proc2 is created, proc1 will reference this new stored procedure now, you do not have to recompile proc1.

After a stored procedure group, a single stored procedure in the group cannot be deleted. Deleting a stored procedure deletes all stored procedures in the same group.

Drop procedure

Deletes one or more stored procedures or process groups from the current database.

Syntax

Drop procedure {Procedure}[,...N]

Parameters

Procedure

Is the name of the stored procedure or stored procedure group to be deleted. The process name must comply with the identifier rules. For more information, see use identifiers. You can choose whether to specify the process owner name, but not the server name and database name.

N

Yes indicates that multiple process placeholders can be specified.

Note

To view the process name list, useSp_help.To display the process definition (stored inSyscommentsIn the system table), useSp_helptext.When a stored procedure is removedSysobjectsAndSyscommentsThis process is deleted from the system table.

Individual processes in the group cannot be removed. The entire process group must be removed.

Regardless of the user-defined system process (SP _Is the prefix.MasterRemove from the database. If the system process is not found in the current database, Microsoft SQL Server triesMasterRemove the database.

Permission

By default, the drop procedure permission is granted to the process owner, which cannot be transferred. However,Db_ownerAndDb_ddladminFixed database role members andSysAdminA fixed server role member can remove any object by specifying the owner in drop procedure.

Example

Delete the following exampleByroyaltyStored Procedure (in the current database ).

Drop procedure byroyalty

Go

From: http://goaler.xicp.net/ShowLog.asp? Id = 518