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).