How To Drop Temporary SP If Exists in Sql Server 2005

w80xi6nr 于 2023-10-15 发布在 SQL Server

关注(0)|答案(3)|浏览(116)

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!

sql-server

来源：https://stackoverflow.com/questions/6569655/how-to-drop-temporary-sp-if-exists-in-sql-server-2005

3条答案

按热度按时间

jjhzyzn01#

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

赞(0）回复(0）举报 2023-10-15

jbose2ul2#

NOT 2005 but with modern SQL server you can do:

DROP PROCEDURE IF EXISTS  #TestTempSP;

赞(0）回复(0）举报 2023-10-15

kxeu7u2r3#

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

赞(0）回复(0）举报 2023-10-15

我来回答

How To Drop Temporary SP If Exists in Sql Server 2005

3条答案

相关问题

热门标签

最新问答