Sunday, October 18, 2015

SQL - Delete or Drop Stored Procedure

As we have learnt to create stored procedure and alter stored procedure of the existing stored procedure. If we have stored procedure and there is no need to use that in the future then we have to delete that stored procedure from our database. To achieve this task, we will use the drop command to delete that stored procedure. 
Before delete them from our database, we need to check the existence of them in our database as shown below:
Drop Single Stored Procedure
Before dropping any single stored procedure, we should always check the existence of the object as given below: 
---- Check the existance of the stored procedure
IF OBJECT_ID('[dbo].[proc_GetEmployeeDetails]') IS NOT NULL
BEGIN
---- Drop the exists stored procedure
Drop proc proc_GetEmployeeDetails

END

Drop Multiple Stored Procedure
We can drop multiple stored procedure in a single statement as given below:
---- Drop the exists stored procedures
Drop proc proc_GetEmployeeDetails, proc_GetEmployeeDetails,
proc_UpdateEmployeeDetails


In the Microsoft SQL Server Management Studio, we can delete the existing stored procedure without using SQL command. 
For this, we need to right click on the existing stored procedure and choose delete as shown below:



This will launch the Delete Object window as shown below:

Now click on the OK button to delete that stored procedure from the database. Learn more on another features of SQL as:

No comments:

Post a Comment