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

Solution 1

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

Solution 2

This script tries to modify a procedure that already exists; it doesn't create the procedure.

To create the procedure use CREATE PROCEDURE

CREATE PROCEDURE [hgomez].[NewQuestion]

Once the procedure exists, you can modify its definition by using ALTER PROCEDURE

ALTER PROCEDURE [hgomez].[NewQuestion]

Solution 3

This solution https://stackoverflow.com/a/26775310/2211788 explained

If you drop and re-create a stored procedure it gets a new objectid - the list of stored procedures in SSMS is linked to the id it knows at the time the list was built. If you re-create it but don't refresh the stored procedures folder then any attempts to edit it will indicate the procedure is not found as the id has changed.

Solution 4

This happened to me once when I had two instances of SSMS open and I was working on the one I opened first. Closed them both down, reopened and it worked fine.

View more solutions

Comments

[Andriy M]

Nice idea about a stub, never thought about it. I might probably choose to put PRINT instead of SELECT, though, but that may be just a matter of taste.

[HGomez]

Quick solution. I must have overlooked the alter statement. Thanks