Invalid Object Name - Stored Procedure

Question

I am creating a stored procedure in SQL Server via SSMS.

I have written the stored procedure below, however when I click execute it am given the error:

Msg 208, Level 16, State 6, Procedure NewQuestion, Line 11 Invalid object name 'hgomez.NewQuestion'.

the table is ownership is correct. (hgomez.Questions)

USE [devworks_oscar]
GO
/****** Object:  StoredProcedure [hgomez].[NewQuestion]    Script Date: 10/23/2011 23:55:08 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
ALTER PROCEDURE [hgomez].[NewQuestion]
    (
    @QUESTIONNAME nvarchar(50),
    @QUESTION_ID int OUTPUT
    )

AS
    /* SET NOCOUNT ON */
    INSERT INTO [Questions] (QuestionText) VALUES (@QUESTIONNAME)
    SET @QUESTION_ID = SCOPE_IDENTITY();
    RETURN

Thanks in advance

Does your stored procedure exist? This error indicates that it can't find the object, in this case your SP. Did you previously create it with a create procedure? — Steven Mastandrea

billinkc billinkc · Accepted Answer · 2011-10-24T02:22:05

I was a fan of always prepending my CREATE statements with an explicit check for existence and dropping if it was found.

IF EXISTS (SELECT * FROM INFORMATION_SCHEMA.ROUTINES WHERE ROUTINE_NAME = 'NewQuestion' AND ROUTINE_SCHEMA = 'hgomez')
BEGIN
    DROP PROCEDURE hgomez.NewQuestion
END
GO

-- this is always a CREATE
CREATE PROCEDURE [hgomez].[NewQuestion]
    (
    @QUESTIONNAME nvarchar(50),
    @QUESTION_ID int OUTPUT
    )

AS
    /* SET NOCOUNT ON */
    INSERT INTO [Questions] (QuestionText) VALUES (@QUESTIONNAME)
    SET @QUESTION_ID = SCOPE_IDENTITY();
    RETURN

That can be a bit of hassle with regard to permissions so others use an approach wherein they create a stub method only to immediately ALTER it.

IF NOT EXISTS (SELECT * FROM INFORMATION_SCHEMA.ROUTINES WHERE ROUTINE_NAME = 'NewQuestion' AND ROUTINE_SCHEMA = 'hgomez')
BEGIN
    EXEC ('CREATE PROCEDURE hgomez.NewQuestion AS SELECT ''stub version, to be replaced''')
END
GO

-- This is always ALTER
ALTER PROCEDURE [hgomez].[NewQuestion]
    (
    @QUESTIONNAME nvarchar(50),
    @QUESTION_ID int OUTPUT
    )

AS
    /* SET NOCOUNT ON */
    INSERT INTO [Questions] (QuestionText) VALUES (@QUESTIONNAME)
    SET @QUESTION_ID = SCOPE_IDENTITY();
    RETURN

Invalid Object Name - Stored Procedure

4 Answers