无效的对象名称-存储过程

小编典典

无效的对象名称-存储过程

sql

我正在通过SSMS在SQL Server中创建一个存储过程。

我已经在下面编写了存储过程，但是当我单击“ 执行” 时，出现错误：

消息208，级别16，状态6，过程NewQuestion，行11无效的对象名称“ hgomez.NewQuestion”。

该表的所有权是正确的。（hgomez。问题）

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

提前致谢

阅读 149

2021-05-16

共1个答案

小编典典

我非常喜欢始终在CREATE语句前先进行显式检查是否存在，如果发现则丢弃。

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

关于权限，这可能有点麻烦，因此其他人使用一种方法，即他们创建存根方法只是立即将ALTER其存入。

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

2021-05-16