| title | DROP PROCEDURE (Transact-SQL) | Microsoft Docs | ||||||||
|---|---|---|---|---|---|---|---|---|---|
| ms.custom |
|
||||||||
| ms.date | 10/28/2015 | ||||||||
| ms.prod | sql-non-specified | ||||||||
| ms.reviewer | |||||||||
| ms.suite | |||||||||
| ms.technology |
|
||||||||
| ms.tgt_pltfrm | |||||||||
| ms.topic | language-reference | ||||||||
| f1_keywords |
|
||||||||
| dev_langs |
|
||||||||
| helpviewer_keywords |
|
||||||||
| ms.assetid | 1c2d7235-7b9b-4336-8f17-429e7d82c2c3 | ||||||||
| caps.latest.revision | 51 | ||||||||
| author | BYHAM | ||||||||
| ms.author | rickbyh | ||||||||
| manager | jhubbard |
[!INCLUDEtsql-appliesto-ss2008-all_md]
Removes one or more stored procedures or procedure groups from the current database in [!INCLUDEssCurrent].
Transact-SQL Syntax Conventions
-- Syntax for SQL Server and Azure SQL Database
DROP { PROC | PROCEDURE } [ IF EXISTS ] { [ schema_name. ] procedure } [ ,...n ] -- Syntax for Azure SQL Data Warehouse and Parallel Data Warehouse
DROP { PROC | PROCEDURE } { [ schema_name. ] procedure_name } IF EXISTS
| Applies to: [!INCLUDEssNoVersion] ([!INCLUDEssSQL15] through current version). |
Conditionally drops the procedure only if it already exists.
schema_name
The name of the schema to which the procedure belongs. A server name or database name cannot be specified.
procedure
The name of the stored procedure or stored procedure group to be removed. Individual procedures within a numbered procedure group cannot be dropped; the whole procedure group is dropped.
Before removing any stored procedure, check for dependent objects and modify these objects accordingly. Dropping a stored procedure can cause dependent objects and scripts to fail when these objects are not updated. For more information, see View the Dependencies of a Stored Procedure
To display a list of existing procedures, query the sys.objects catalog view. To display the procedure definition, query the sys.sql_modules catalog view.
Requires CONTROL permission on the procedure, or ALTER permission on the schema to which the procedure belongs, or membership in the db_ddladmin fixed server role.
The following example removes the dbo.uspMyProc stored procedure in the current database.
DROP PROCEDURE dbo.uspMyProc;
GO
The following example removes several stored procedures in the current database.
DROP PROCEDURE dbo.uspGetSalesbyMonth, dbo.uspUpdateSalesQuotes, dbo.uspGetSalesByYear;
The following example removes the dbo.uspMyProc stored procedure if it exists but does not cause an error if the procedure does not exist. This syntax is new in [!INCLUDEssSQL15].
DROP PROCEDURE IF EXISTS dbo.uspMyProc;
GO
The following example removes the dbo.uspMyProc stored procedure in the current database.
DROP PROCEDURE dbo.uspMyProc;
GO
ALTER PROCEDURE (Transact-SQL)
CREATE PROCEDURE (Transact-SQL)
sys.objects (Transact-SQL)
sys.sql_modules (Transact-SQL)
Delete a Stored Procedure