How To Drop Temporary SP If Exists in Sql Server 2

问题:

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!

回答1:

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

回答2:

IF EXISTS (SELECT * FROM sys.procedures WHERE name = 'baz')
DROP PROCEDURE baz