DROP PROCEDURE (Transact-SQL)

Removes one or more existing stored procedures.

Applies to: SQL Server, Azure SQL Database, Azure Synapse Analytics, Parallel Data Warehouse

Syntax

DROP PROCEDURE [ IF EXISTS ] { [ database_name. ][ schema_name. ] procedure_name

[ ,...n ]

Description

DROP PROCEDURE removes the specified stored procedure from the database. You can drop multiple procedures with a single statement.

If a procedure is the target of a DROP PROCEDURE statement, and is referenced by other objects, the procedure must be dropped first before the dependent objects can be dropped. However, if the procedure is referenced by a computed column or a default constraint, the dependent objects can still be dropped.

The IF EXISTS option can be used to conditionally drop a procedure only if it already exists.

Arguments

Parameter	Description
`IF EXISTS`	Conditionally drops the procedure only if it already exists.
`database_name`	The name of the database containing the procedure.
`schema_name`	The name of the schema to which the procedure belongs.
`procedure_name`	The name of the stored procedure to remove.

Permissions

Requires ALTER permission on the schema that contains the procedure, or CONTROL permission on the procedure itself.

Examples

A. Dropping a single procedure

DROP PROCEDURE Production.usp_UpdateOrderDetails;

This statement drops the usp_UpdateOrderDetails stored procedure in the Production schema.

B. Dropping multiple procedures

DROP PROCEDURE dbo.usp_GetCustomerInfo, dbo.usp_UpdateCustomerAddress;

This statement drops two stored procedures, usp_GetCustomerInfo and usp_UpdateCustomerAddress, both in the dbo schema.

C. Conditionally dropping a procedure

DROP PROCEDURE IF EXISTS Sales.usp_CalculateSalesTax;

This statement drops the usp_CalculateSalesTax stored procedure in the Sales schema, but only if it exists.