| description | DROP PROCEDURE (Transact-SQL) | ||||||||
|---|---|---|---|---|---|---|---|---|---|
| title | DROP PROCEDURE (Transact-SQL) | Microsoft Docs | ||||||||
| ms.custom | |||||||||
| ms.date | 05/11/2017 | ||||||||
| ms.prod | sql | ||||||||
| ms.prod_service | database-engine, sql-database, synapse-analytics, pdw | ||||||||
| ms.reviewer | |||||||||
| ms.technology | t-sql | ||||||||
| ms.topic | reference | ||||||||
| f1_keywords |
|
||||||||
| dev_langs |
|
||||||||
| helpviewer_keywords |
|
||||||||
| ms.assetid | 1c2d7235-7b9b-4336-8f17-429e7d82c2c3 | ||||||||
| author | WilliamDAssafMSFT | ||||||||
| ms.author | wiassaf | ||||||||
| monikerRange | >=aps-pdw-2016||=azuresqldb-current||=azure-sqldw-latest||>=sql-server-2016||>=sql-server-linux-2017||=azuresqldb-mi-current |
[!INCLUDE sql-asdb-asdbmi-asa-pdw]
Removes one or more stored procedures or procedure groups from the current database in [!INCLUDEssnoversion].
Transact-SQL Syntax Conventions
-- Syntax for SQL Server and Azure SQL Database
DROP { PROC | PROCEDURE } [ IF EXISTS ] { [ schema_name. ] procedure } [ ,...n ]
-- Syntax for Azure Synapse Analytics and Parallel Data Warehouse
DROP { PROC | PROCEDURE } { [ schema_name. ] procedure_name }
[!INCLUDEsql-server-tsql-previous-offline-documentation]
IF EXISTS
Applies to: [!INCLUDEssNoVersion] ( [!INCLUDEsssql16-md] 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 [!INCLUDEsssql16-md].
DROP PROCEDURE IF EXISTS dbo.uspMyProc;
GO ALTER PROCEDURE (Transact-SQL)
CREATE PROCEDURE (Transact-SQL)
sys.objects (Transact-SQL)
sys.sql_modules (Transact-SQL)
Delete a Stored Procedure