SQL 错误时从存储过程返回值

Return value from a stored proc on error

提问by

I have an sp in SQL Server that when errors returns -4

我在 SQL Server 中有一个 sp，当错误返回 -4

What does -4 mean? Is there a table somewhere explaining what the possible return values are?

-4 是什么意思？是否有表格解释可能的返回值是什么？

---

---

There must be some standard

一定有标准

For example

例如

declare @RetVal int  
EXEC @RetVal = stpTest  
select @RetVal

where stpTest is "SELECT 1/0" returns -6.

其中 stpTest 是“SELECT 1/0”返回-6。

-6 must mean something!

-6 一定意味着什么！

---

---

The sp that returned -4 only has UPDATE and SELECT INTO statements in it.

返回 -4 的 sp 中只有 UPDATE 和 SELECT INTO 语句。

At no point does it do 'SELECT -4' so how can I "find out what -4 means in that particular stored procedure"?

它在任何时候都不会执行 'SELECT -4' 那么我如何才能“找出 -4 在该特定存储过程中的含义”？

Also, if there is no standard then why does a divide by zero error always return -6?

另外，如果没有标准，那么为什么除以零错误总是返回-6？

---

---

If you have an sp that does not return anything i.e. it doesn't have any select statements in it and you do:

如果您有一个不返回任何内容的 sp，即其中没有任何 select 语句并且您执行以下操作：

declare @RetVal int  
EXEC @RetVal = yourSPName  

Then @RetVal will have a value of 0.

那么@RetVal 的值为 0。

If there is an error then @RetVal will be a value other then zero, for example if the only thing your sp does is "SELECT 1/0" then @RetVal will be -6.

如果出现错误，那么@RetVal 将是一个非零值，例如，如果您的 sp 所做的唯一事情是“SELECT 1/0”，那么@RetVal 将为 -6。

Try it and see

试试看

My question is what do these return values mean? They must have some logical meaning!

我的问题是这些返回值是什么意思？它们必须具有某种逻辑意义！

回答by

If you have a RETURN statement with an explicit return value, that is of course the return value.

如果您有一个带有显式返回值的 RETURN 语句，那当然是返回值。

But if there is no RETURN statement, but an error occurs during execution, the return value is 10 minus the severity level of the error. Division by zero is level 16, thus the return value is -6. Permissions errors are typical level 14, thus the return value is -4.

但是如果没有RETURN语句，但是在执行过程中出现错误，则返回值为10减去错误的严重程度。除以零是级别 16，因此返回值为 -6。权限错误是典型的 14 级，因此返回值为 -4。

As you may guess this is not terribly useful, but this: 0 is success, and everything else is an error.

正如您可能猜到的，这不是很有用，但是： 0 是成功，其他一切都是错误。

回答by John Saunders

There is no standard for return codes. You'll have to find out what -4 means in that particular stored procedure. In fact, not all return codes are errors.

返回代码没有标准。您必须找出 -4 在该特定存储过程中的含义。事实上，并非所有返回码都是错误。

---

---

EDIT: counter-example

编辑：反例

SET ANSI_NULLS ON
GO

SET QUOTED_IDENTIFIER ON
GO

CREATE PROCEDURE [dbo].[RetValTest] 
AS
BEGIN
    select 1/0;
END

GO

Execution:

执行：

DECLARE @return_value int

EXEC    @return_value = [dbo].[RetValTest]

SELECT  'Return Value' = @return_value

GO

Result:

结果：

Msg 8134, Level 16, State 1, Procedure RetValTest, Line 9
Divide by zero error encountered.

This is with SQL Server 2008.

这是 SQL Server 2008。

---

---

Some research suggests that this behavior may be left over from SQL Server 6.0. If that is the case, then you can decide for yourself how reliable it's likely to be, given that they stopped documenting it (and stopped guaranteeing its accuracy) so long ago.

一些研究表明，这种行为可能是 SQL Server 6.0 遗留下来的。如果是这种情况，那么您可以自己决定它可能有多可靠，因为他们很久以前就停止记录它（并停止保证其准确性）。

---

---

My "research" is due, with thanks, to SQL Server MVP Tibor Karaszi. His source is Books Online for SQL Server 6.5. Under ""Control-Of-Flow Language", RETURN", he found

我的“研究”归功于 SQL Server MVP Tibor Karaszi。他的资料来源是 SQL Server 6.5 的联机丛书。在“Control-Of-Flow Language”, RETURN 下，他发现

"SQL Server reserves 0 to indicate a successful return and reserves negative values from - 1 through - 99 to indicate different reasons for failure. If no user-defined return value is provided, the SQL Server value is used. User-defined return status values should not conflict with those reserved by SQL Server. The values 0 through -14 are currently in use.

"SQL Server 保留 0 表示成功返回，保留 - 1 到 - 99 的负值表示不同的失败原因。如果未提供用户定义的返回值，则使用 SQL Server 值。用户定义的返回状态值不应与 SQL Server 保留的值冲突。值 0 到 -14 当前正在使用。

回答by Jim G.

For example

declare @RetVal int  
EXEC @RetVal = stpTest  
select @RetVal

where stpTestis SELECT 1/0returns -6.

-6 must mean something!

If you have an sp that does not return anything i.e. it doesn't have any select statements in it and you do:

declare @RetVal int  
EXEC @RetVal = yourSPName  

Then @RetValwill have a value of 0.

If there is an error then @RetValwill be a value other then zero, for example if the only thing your sp does is SELECT 1/0then @RetValwill be -6.

Try it and see

例如

declare @RetVal int  
EXEC @RetVal = stpTest  
select @RetVal

哪里stpTest是SELECT 1/0返回-6。

-6 一定意味着什么！

如果您有一个不返回任何内容的 sp，即其中没有任何 select 语句并且您执行以下操作：

declare @RetVal int  
EXEC @RetVal = yourSPName  

然后@RetVal将有一个值为 0。

如果有错误，那么@RetVal将是例如其他然后零值，如果你的SP做的唯一事情就是SELECT 1/0那么 @RetVal将是-6。

试试看

---

---

First of all, thanks for bothering to craft an example that returns -6.

首先，感谢您费心制作一个返回 -6 的示例。

Here's what the documentation says about -6:

以下是文档中关于 -6 的说明：

-6 Miscellaneous user error occurred.

-6 发生其他用户错误。

-6 might be the most amorphous code that SQL Server returns.
Why? Because the -6 error code is essentially hiding an error deeper in the call stack.

-6 可能是 SQL Server 返回的最无定形的代码。
为什么？因为 -6 错误代码本质上是在调用堆栈中更深地隐藏了一个错误。

After troubleshooting this error myself, here are my tips for troubleshooting this error:

在自己解决此错误后，以下是我解决此错误的提示：

1. If your DAL or application layer generates this error, then run the SQL code in SQL Server Management Studio as the application user, notwith your own SQL Server ID.[Why? Because your permissions might not be the same.]
2. Make the SQL code under inspection as readable as possible. [Why? Because this error may lurk deep within the call stack.]
3. Failing all else, comment out half of the code. Is the error still occurring? Comment out 50% of the remaining code. Rinse and repeat.

1. 如果您的 DAL 或应用程序层生成此错误，请以应用程序用户的身份在 SQL Server Management Studio 中运行 SQL 代码，而不是使用您自己的 SQL Server ID。[为什么？因为您的权限可能不一样。]
2. 使检查中的 SQL 代码尽可能具有可读性。[为什么？因为这个错误可能潜伏在调用堆栈的深处。]
3. 其他都失败了，注释掉一半的代码。错误还在发生吗？注释掉剩余代码的 50%。冲洗并重复。

回答by Paul Rowland

I think the question should be edited to ask -

我认为应该编辑这个问题来问 -

What are the default return values if you dont have a RETURN statement in your stored proc?

如果您的存储过程中没有 RETURN 语句，默认返回值是什么？

Something I did find was from this link www.redware.com/handbooks/sql_server_handbook/sql_server_stored_procedures.html

我找到的东西来自这个链接www.redware.com/handbooks/sql_server_handbook/sql_server_stored_procedures.html

SQL Server will default the return value to zero. The returned values are typically used to return a status flag from the stored procedure with a non-zero value usually indicating failure during processing.

Returned values are difficult to access using ODBC their use is recommended only to return a success or failure of the stored procedure when communicating with other stored procedures.

SQL Server 会将返回值默认为零。返回值通常用于从存储过程返回状态标志，其中非零值通常表示处理过程中失败。

使用 ODBC 很难访问返回值，建议仅在与其他存储过程通信时返回存储过程的成功或失败时使用它们。

From this link - sqlserverpedia.com/wiki/Stored_Procedures_-_Output_Parameters_&_Return_Values

从这个链接 - sqlserverpedia.com/wiki/Stored_Procedures_-_Output_Parameters_&_Return_Values

The return values -99 through 0 are reserved for SQL Server internal use. You can create your own parameters that can be passed back to the calling program.

返回值 -99 到 0 保留供 SQL Server 内部使用。您可以创建自己的参数，这些参数可以传递回调用程序。

Also another link (I guess) from @Erland Sommarskog www.sommarskog.se/error-handling-I.html

还有另一个链接（我猜）来自@Erland Sommarskog www.sommarskog.se/error-handling-I.html

Return Values from Stored Procedures

All stored procedures have a return value, determined by the RETURN statement. The RETURN statement takes one optional argument, which should be a numeric value. If you say RETURN without providing a value, the return value is 0 if there is no error during execution. If an error occurs during execution of the procedure, the return value may be 0, or it may be a negative number. The same is true if there is no RETURN statement at all in the procedure: the return value may be a negative number or it may be 0.

Whether these negative numbers have any meaning, is a bit difficult to tell. It used to be the case, that the return values -1 to -99 were reserved for system-generated return values, and Books Online for earlier versions of SQL Server specified meanings for values -1 to -14. However, Books Online for SQL 2000 is silent on any such reservations, and does not explain what -1 to -14 would mean.

With some occasional exception, the system stored procedures that Microsoft ships with SQL Server return 0 to indicate success and any non-zero value indicates failure.

从存储过程返回值

所有存储过程都有一个返回值，由 RETURN 语句确定。RETURN 语句采用一个可选参数，它应该是一个数值。如果在没有提供值的情况下说RETURN，如果在执行过程中没有错误，则返回值为0。如果在程序执行过程中发生错误，返回值可能是0，也可能是负数。如果过程中根本没有 RETURN 语句也是如此：返回值可能是负数，也可能是 0。

这些负数是否有任何意义，有点难以判断。过去的情况是，返回值 -1 到 -99 是为系统生成的返回值保留的，并且早期版本的 SQL Server 的联机丛书指定了值 -1 到 -14 的含义。但是，SQL 2000 联机丛书对任何此类保留都没有提及，并且没有解释 -1 到 -14 的含义。

除了一些偶然的例外，Microsoft 随 SQL Server 提供的系统存储过程返回 0 表示成功，任何非零值表示失败。

Getting Error Information

获取错误信息

Also if you need to find what the error is (rather than what -6 means) you could try putting your sql into a try catch, ie.

此外，如果您需要查找错误是什么（而不是 -6 的含义），您可以尝试将 sql 放入 try catch 中，即。

begin try

    select 1/0 as 'an error'

end try

begin catch

    select ERROR_NUMBER() as 'ERROR_NUMBER', 
           ERROR_SEVERITY() as 'ERROR_SEVERITY',
           ERROR_STATE() as 'ERROR_STATE',
           LEFT(ERROR_PROCEDURE(),50) as 'ERROR_PROCEDURE',
           ERROR_LINE() as 'ERROR_LINE' , 
           LEFT(ERROR_MESSAGE(),40) as 'ERROR_MESSAGE'    
end catch 

回答by Rob

I'm not sure there's a way to know this without asking the SQL Server devs. It's something lower level than your stored proc evaluating what you're sending it and generating that return code. Unless you've specifically got a RETURN -4in your code, it's coming from the SQL parser probably.

我不确定有没有办法在不询问 SQL Server 开发人员的情况下知道这一点。它比您存储的 proc 评估您发送的内容并生成该返回代码的级别低。除非您在代码中特别指定了RETURN -4，否则它可能来自 SQL 解析器。