Menu
My Question is simple! How to Drop a Temporary Stored Procedure If Exists ? This is because while I create a Temporary SP in a script, it throws error like 'There is already an object named '#sp_name' in the database' while i run the Second time. I dont want to show this message to users. Please help me. Your solution is Highly appereciated!
861
Drop store procedure if exists: To drop the procedure, we have to write a conditional statement to check if the store procedure exists or not then write the drop statement. Otherwise, it will raise an error in case the stored procedure does not exist.
In SQL Server, we can use the OBJECT_ID function to get the table name of the temporary table, and if the table is found, we can use the DROP TABLE statement to drop the temp table in sql. Another method we learned is to use the DROP TABLE IF EXISTS statement.
Use SQL Server Management Studio Expand Databases, expand the database in which the procedure belongs, and then expand Programmability. Expand Stored Procedures, right-click the procedure to remove, and then select Delete.
SQL Server Management Studio Activity Monitor Once Activity Monitor has loaded, expand the 'Processes' section. Scroll down to the SPID of the process you would like to kill. Right click on that line and select 'Kill Process'. A popup window will open for you to confirm that you want to kill the process.
The temp procs are dropped in the same way as permanent procs are dropped. Please see the code below:
-- Create test temp. proc
CREATE PROC #tempMyProc as
Begin
print 'Temp proc'
END
GO
-- Drop the above proc
IF OBJECT_ID('tempdb..#tempMyProc') IS NOT NULL
BEGIN
DROP PROC #tempMyProc
END
If you love us? You can donate to us via Paypal or buy me a coffee so we can maintain and grow! Thank you!
Donate Us With
