drop procedure

Removes a procedure.

Syntax

drop proc[edure] [owner.]procedure_name 
	[, [owner.]procedure_name] ... 

Parameters

Examples

Usage

  • drop procedure drops user-defined stored procedures, system procedures, and extended stored procedures (ESPs).

  • The SAP ASE server checks the existence of a procedure each time a user or a program executes that procedure.

  • A procedure group (more than one procedure with the same name but with different number suffixes) can be dropped with a single drop procedure statement. For example, if the procedures used with the application named orders were named orderproc;1, orderproc;2, and so on, the following statement drops the entire group:
    drop proc orderproc
    Once procedures have been grouped, individual procedures within the group cannot be dropped. For example, the following statement is not allowed:
    drop procedure orderproc;2

    You cannot drop extended stored procedures as a procedure group.

  • sp_helptext displays the procedure’s text, which is stored in syscomments.

  • sp_helpextendedproc displays ESPs and their corresponding DLLs.

  • Dropping an ESP unregisters the procedure by removing it from the system tables. It has no effect on the underlying DLL.

  • drop procedure drops only user-created stored procedures from your current database.

See also sp_depends, sp_dropextendedproc, sp_helptext, sp_rename in Reference Manual: Procedures.

Standards

ANSI SQL – Compliance level: Transact-SQL extension.

Permissions

The permission checks for drop procedure differ based on your granular permissions settings.

SettingDescription
Enabled

With granular permissions enabled, you must be the procedure owner or a user with drop any procedure privilege.

Disabled

With granular permissions disabled, you must be the procedure owner or a user with sa_role.

Auditing

Values in event and extrainfo columns of sysaudits are:

InformationValues
Event

28

Audit option

drop

Command or access audited

drop procedure

Information in extrainfo
  • Roles – current active roles

  • Keywords or options – NULL

  • Previous value – NULL

  • Current value – NULL

  • Other information – NULL

  • Proxy information – original login name, if set proxy is in effect

Related reference
create procedure
create procedure (SQLJ)