Deleting a stored procedure can no longer be deleted when you need a stored procedure. If another stored procedure calls a deleted stored procedure, Microsoft® SQL ServerTM 2000 displays an error message when the call is executed. However, if you define a new stored procedure with the same name and parameter to replace the deleted stored procedure, then other processes that reference the process can still be performed smoothly. For example, if the stored procedure proc1 references stored procedure proC2, PROC2 is deleted, but the stored procedure called ProC2 is created, and now ProC1 will reference this new stored procedure, PROC1 does not have to recompile.
After the stored procedure is packet, you will not be able to delete a single stored procedure within the group. Deleting a stored procedure will delete all stored procedures in the same group.
Drop Procedure
Delete one or more stored procedures or process groups from the current database.
grammar
DROP procedure {procedure} [, ... n]
parameter
Procedure
Is the name of the stored procedure or stored procedure group to be deleted. The process name must meet the identifier rules. For more information, see Using the Identifier. You can choose whether to specify the process owner name, but you cannot specify the server name and database name.
n
It is a placeholder that represents a plurality of processes.
Comment
To view a list of procedures, use sp_help. To display the process definition (stored in the SysComments system table), use sp_helptext. When a stored procedure is removed, the information about the process will be removed from the SysObjects and the SysComments system table.
The individual processes in the group cannot be removed, and the entire process group must be removed.
Regardless of whether the user-defined system procedure (with SP_ is prefix) is the current database, it is removed from the Master database. If a system procedure is not found at the current database, Microsoft® SQL ServerTM attempts to remove it from the Master database.
Authority
By default, the Drop Procedure Permissions grant the process owner, which is not transferable. However, DB_OWNER and DB_DDLADMIN fixed database role members and sysadmin fixed server role members can remove any objects by specifying the owner in Drop Procedure.
Example
The next example deletes the ByroyalTy stored procedure (in the current database).
Drop Procedure Byroyalty
Go
