CODEWITHSUNDEEP
sqlsql-serverstored-procedures
Paul
Paul

Reputation: 16853

Stored Procedure Does and Does Not Exist

I have an SQL Server 2008 R2 database with a stored procedure that may or may not exist.

If I run sp_help spThing, it returns a row as if it existed (Name: spThing, Owner: dbo, Type: stored procedure).

If I try to create a new stored procedure with the same name, I get the error "There is already an object named 'spThing' in the database".

BUT

If I run SELECT OBJECT_ID('dbo.spThing'), it returns NULL.

If I run EXEC spThing, it says "Could not find stored procedure 'spThing'".

If I run DROP spThing, it says "Cannot drop the procedure 'spThing', because it does not exist or you do not have permission".

If I run SELECT * FROM sys.objects WHERE name = 'spThing', I get no row.

If I run SELECT * FROM INFORMATION_SCHEMA.ROUTINES WHERE ROUTINE_NAME = 'spThing', I get no row.

All these were run from the same connection, as an administrative user.

Does the stored procedure exist?

Edit:

The SP itself is a trivial select, along the lines of:

CREATE PROCEDURE spThing
    @Param int
AS
BEGIN

    SELECT strThing
    FROM tblThing
    WHERE lngParam = @Param;

END

Upvotes: 4

Views: 6433

Answers (3)

Chirag
Chirag

Reputation: 4184

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

CREATE PROCEDURE [dbo].[spThing]
AS
BEGIN 

-- Code here

END
GO

Upvotes: 1

Tan Suiwseng
Tan Suiwseng

Reputation: 77

For my answer, it exists in dbo.spThing but not exist in sys.spThing

i do not have specific answer:

there is 2 type of store procedure , 1 is for system, and i is for databaseobject. The 1 paul and devart try is to create a dbo.spthing.

firstly, paul say if SELECT * FROM sys.objects WHERE name = 'spThing' return no row mean not exists in system stored procedure and devart try is create in dbo.Spthing and will return result.

just wondering ,your question is correct on this line or not SELECT OBJECT_ID('dbo.spThing') because it will exist instead of not exist

further more , you did run sp_help spThing and owner is dbo so your dbo.spthing is exist. but not sys.spthing

Upvotes: 0

Devart
Devart

Reputation: 122032

All works fine -

IF OBJECT_ID ('dbo.spThing') IS NOT NULL
     DROP PROCEDURE dbo.spThing
GO

SELECT OBJECT_ID('dbo.spThing')
GO

CREATE PROCEDURE dbo.spThing

    @Param INT

AS BEGIN

    SELECT strThing
    FROM dbo.tblThing
    WHERE lngParam = @Param

END
GO

SELECT OBJECT_ID('dbo.spThing')

Output -

-----------
NULL
(1 row(s) affected)

-----------
664283184
(1 row(s) affected)

Upvotes: 5

Related Questions