Drop and re-create procedure if it exists in T-SQL not working

Question

IF EXISTS ( SELECT  * FROM    sys.objects WHERE   object_id = OBJECT_ID(N'LOCATION') AND type IN (N'P', N'PC')) 
DROP PROCEDURE [dbo].[LOCATION]
GO

CREATE PROCEDURE [dbo].[LOCATION]
    @IP NVARCHAR(100)
AS
BEGIN
DECLARE @IPNumber BIGINT

SELECT @IPNumber = dbo.ConvertIp2Num(@IP)

    SELECT [country_code],[country_name]
    FROM [myDatabase].[dbo].[CountryIP]
    WHERE @IPNumber BETWEEN ip_from AND ip_to
END

I have the above code to check if stored procedure LOCATION exists in the current database. I expect it to drop and re-create the procedure if it exists.

However, if the procedure exists the code is still executing and as a result i get the following error 'There is already an object named 'LOCATION' in the database.'

Why is that code failing to drop the procedure if it exists?

The same code works properly for a another procedure in the same database.

Answer 1

Try this (preferred method using a view):

IF EXISTS(SELECT 1
          FROM   INFORMATION_SCHEMA.ROUTINES
          WHERE  ROUTINE_NAME = 'PRC_NAME'
                 AND SPECIFIC_SCHEMA = 'schema_name')
  BEGIN
      DROP PROCEDURE PRC_NAME
  END

or this (not recommended using direct access to a system table):

IF EXISTS (SELECT 1
           FROM   SYS.PROCEDURES
           WHERE  NAME = 'PRC_NAME'
                  AND SCHEMA_NAME(SCHEMA_ID) = 'SCHEMA_NAME'
                  AND [TYPE] IN (N'P',N'PC'))
  BEGIN
      DROP PROCEDURE PRC_NAME
  END

Why the first method is preferred you can find out for example in this question: SQL Server: should I use information_schema tables over sys tables?

Answer 2

This is kind of late, but others that end up here might want to check out the MSDN documentation that say you could use:

DROP PROCEDURE IF EXISTS dbo.uspMyProc;
GO

This is however available from SQL Server 2016 Community Technology Preview 3.3 (CTP 3.3).

Answer 3

You could use:

IF OBJECT_ID('MSL_GET_IP_LOCATION', 'P') IS NOT NULL
  DROP PROCEDURE MSL_GET_IP_LOCATION
GO

Further thought on this is you will need to make sure you have unique names across all objects.

Answer 4

SQL Server - Drop List of Stored Procedures if existed on Customer DB + Copy List of Stored Procedures from master to another DB (recreate dynamically). P.S.: @TargetDBName=your DB. I hope it will help someone

--Drop SPs from Customer DB if existed---
DECLARE @TargetDBName NVARCHAR(255)
SET @TargetDBName = DB_NAME()

DECLARE @SQL NVARCHAR(max)
SET @SQL = ''

DECLARE v CURSOR
FOR
SELECT [NAME]
FROM [Master].[sys].[procedures] p WITH(NOLOCK)
INNER JOIN [Master].sys.sql_modules m WITH(NOLOCK) ON p.object_id = m.object_id
WHERE p.[NAME] LIKE 'mySPs_list_%'
AND [type] = 'P'
OPEN v

FETCH NEXT
FROM v
INTO @sql

WHILE @@FETCH_STATUS = 0
BEGIN
SET @sql = REPLACE(@sql, '''', '''''')
SET @sql = 'USE [' + @TargetDBName + ']; IF OBJECT_ID('''+@sql+''', ''P'') IS NOT NULL DROP PROCEDURE '+ @sql+';'

EXEC SP_EXECUTESQL @sql

FETCH NEXT

FROM v

INTO @sql

END

CLOSE v
DEALLOCATE v;

--COPY SPs from master to Another DB-------------
DECLARE c CURSOR
FOR
SELECT [Definition]
FROM [Master].[sys].[procedures] p
INNER JOIN [Master].sys.sql_modules m ON p.object_id = m.object_id
WHERE p.[NAME] LIKE 'mySPs_list_%'
AND [type] = 'P'
OPEN c

FETCH NEXT
FROM c
INTO @sql

WHILE @@FETCH_STATUS = 0
BEGIN
SET @sql = REPLACE(@sql, '''', '''''')
SET @sql = 'USE [' + @TargetDBName + ']; EXEC(''' + @sql + ''')'

EXEC SP_EXECUTESQL @sql

FETCH NEXT
FROM c
INTO @sql

END

CLOSE c
DEALLOCATE c;