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

StackTrace
  • 9,190
  • 36
  • 114
  • 202
  • try `object_id = OBJECT_ID(N'[dbo].[MSL_GET_IP_LOCATION]')` – artm Oct 30 '14 at 07:33
  • @artm same error. The same code works for properly for a another procedure in the same database. – StackTrace Oct 30 '14 at 07:35
  • 1
    Does `select` return anything if you exclude `AND type IN (N'P', N'PC')` ? – artm Oct 30 '14 at 07:37
  • @artm yes the SELECT returns 1 row with the details of the stored procedure. – StackTrace Oct 30 '14 at 07:44
  • Which suggests that the object in question *isn't* a stored procedure - maybe that name has been used for a user-defined function? Notice that the error message just says "an object" but is vague as to what *type* of object the existing one is. – Damien_The_Unbeliever Oct 30 '14 at 07:45
  • @Damien_The_Unbeliever, but if i run the SELECT in the IF EXISTS line, it return 'SQL_STORED_PROCEDURE' for column type_desc? – StackTrace Oct 30 '14 at 07:50
  • Well then, the issue is elsewhere then - if I take the exact code you've posted, replace the body of the procedure with a simple `print` (since I don't have your function or tables) and run it repeatedly, it runs fine - so the problem is somewhere else in code you've not shown us. – Damien_The_Unbeliever Oct 30 '14 at 07:53
  • @SQL.NETWarrior What are the values in `type` and `type_desc` columns if you run the `select` without the `and` ? – artm Oct 30 '14 at 07:55
  • After some more testing, the scripts run successfully as expected if executed independently but i need to execute them in one script file. So in the script file, the first drop and create stored procedure runs successfully, subsequent ones fail. If i make 'MSL_GET_IP_LOCATION' drop and creation the first in the script file, it also runs successfully and the subsequent one that was running successfully when it was at the top of the script file will now fail. – StackTrace Oct 30 '14 at 07:55
  • @artm type returns P and type_desc returns SQL_STORED_PROCEDURE – StackTrace Oct 30 '14 at 07:58
  • 1
    OK, looks like i got it working, if i put GO after BEGIN .....END on all procedures definitions, they all run successfully. – StackTrace Oct 30 '14 at 08:05
  • 1
    Possible duplicate of [How to check if a stored procedure exists before creating it](http://stackoverflow.com/questions/2072086/how-to-check-if-a-stored-procedure-exists-before-creating-it) – Ashraf Sada Jun 12 '16 at 13:06

4 Answers4

13

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?

Community
  • 1
  • 1
Pரதீப்
  • 91,748
  • 19
  • 131
  • 172
13

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

Qben
  • 2,617
  • 2
  • 24
  • 36
8

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.

n34_panda
  • 2,577
  • 5
  • 24
  • 40
0

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;
Hpeck
  • 1
  • 1