SQL 无效的对象名称 - 存储过程
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/7870501/
Warning: these are provided under cc-by-sa 4.0 license. You are free to use/share it, But you must attribute it to the original authors (not me):
StackOverFlow
Invalid Object Name - Stored Procedure
提问by HGomez
I am creating a stored procedure in SQL Server via SSMS.
我正在通过 SSMS 在 SQL Server 中创建一个存储过程。
I have written the stored procedure below, however when I click executeit am given the error:
我写了下面的存储过程，但是当我点击执行它时出现错误：
Msg 208, Level 16, State 6, Procedure NewQuestion, Line 11 Invalid object name 'hgomez.NewQuestion'.
消息 208，级别 16，状态 6，过程 NewQuestion，第 11 行无效的对象名称“hgomez.NewQuestion”。
the table is ownership is correct. (hgomez.Questions)
表的所有权是正确的。(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
提前致谢
回答by billinkc
I was a fan of always prepending my CREATEstatements with an explicit check for existence and dropping if it was found.
我很喜欢总是在我的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
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 ALTERit.
这在权限方面可能有点麻烦，因此其他人使用一种方法，其中他们创建一个存根方法只是为了立即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
回答by Jeff Ogata
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
CREATE PROCEDURE [hgomez].[NewQuestion]
Once the procedure exists, you can modify its definition by using ALTER PROCEDURE
一旦过程存在，您可以通过使用修改其定义 ALTER PROCEDURE
ALTER PROCEDURE [hgomez].[NewQuestion]
回答by Albert
This solution https://stackoverflow.com/a/26775310/2211788explained
这个解决方案https://stackoverflow.com/a/26775310/2211788解释了
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.
如果您删除并重新创建一个存储过程，它会获得一个新的 objectid - SSMS 中的存储过程列表链接到它在构建列表时知道的 id。如果您重新创建它但不刷新存储过程文件夹，则任何对其进行编辑的尝试都将表明未找到该过程，因为 id 已更改。
回答by Todd Vance
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.
当我打开了两个 SSMS 实例并且我正在处理我第一个打开的实例时，这发生在我身上。关闭它们，重新打开，它工作正常。
