错误时从存储过程返回值 [英] Return value from a stored proc on error

问题描述

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

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

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

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

一定有标准

例如

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

其中 stpTest 是 "SELECT 1/0" 返回 -6.

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

-6 一定意味着什么！

-6 must mean something!

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

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

它在任何时候都不会执行SELECT -4"，那么我如何找出 -4 在该特定存储过程中的含义"?

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

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

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

如果您有一个不返回任何内容的 sp，即其中没有任何 select 语句并且您这样做了:

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  

那么@RetVal 的值为 0.

Then @RetVal will have a value of 0.

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

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.

试试看

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

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

推荐答案

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

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

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

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.

正如您可能猜到的那样，这不是很有用，但是:0 表示成功，其他一切都是错误.

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

这篇关于错误时从存储过程返回值的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！