Home

	Expand Search

Reference Manual: Commands

Chapter 1 Commands

drop procedure

drop procedure

Description

Removes a procedure.

Syntax

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

Parameters

is the name of the Transact-SQL or SQLJ procedure to drop. Specify the owner's name to drop a procedure of the same name owned by a different user in the current database. The default value for owner is the current user.

Examples

Example 1

Deletes the stored procedure showind:

drop procedure showind

Example 2

Unregisters the extended stored procedure xp_echo:

drop procedure xp_echo

Usage

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

• Adaptive 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 procedures from your current database.

Standards

ANSI SQL - Compliance level: Transact-SQL extension.

Permissions

drop procedure permission defaults to the procedure owner and is not transferable.

See also

Commands

create procedure, create procedure (SQLJ)

System procedures

sp_depends, sp_dropextendedproc, sp_helpextendedproc, sp_helptext, sp_rename