SQL 如何在存储过程中获取sql错误
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/13647437/
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
How to get sql error in stored procedure
提问by Steve's a D
I'm using SQL Server 2005. I created a stored procedure which works most of the time, but I found an instance of where it doesn't do what I want.
我使用的是 SQL Server 2005。我创建了一个大部分时间都可以工作的存储过程，但是我发现了一个实例，它不能执行我想要的操作。
Currently, the code does something like this
目前，代码做这样的事情
if @@error <> 0
begin
select @message_error = "There was a database error adding product "+ @product + " to product line
end
Where @message_erroris an output variable.
哪里@message_error是输出变量。
So, I can select @@errorand get a number, but all I really want is the SQL error.
所以，我可以select @@error得到一个数字，但我真正想要的只是 SQL 错误。
Something like Hey, I couldn't do this because there is a fk constraint on this columnor whatever. I found this article on msdn http://msdn.microsoft.com/en-us/library/ms178592(v=sql.90).aspx
像嘿，我不能这样做，因为此列或其他内容存在 fk 约束。我在 msdn http://msdn.microsoft.com/en-us/library/ms178592(v=sql.90).aspx上找到了这篇文章
But it only goes over throwing custom exceptions with RAISERROR, I don't want to create my own error message or exception, I just want to know why stuff isn't working. I can execute the stored procedure through Management Studio and see the exact SQL error, but this is tedious trying to match data from the site and manually inserting it that way.
但它只会抛出自定义异常RAISERROR，我不想创建自己的错误消息或异常，我只想知道为什么东西不起作用。我可以通过 Management Studio 执行存储过程并查看确切的 SQL 错误，但是尝试匹配来自站点的数据并以这种方式手动插入它很乏味。
How do I get the SQL error text into an output variable?
如何将 SQL 错误文本输入到输出变量中？
回答by Philip Kelley
Here's part of a stored procedure template I use:
这是我使用的存储过程模板的一部分：
/* CREATE PROCEDURE... */
DECLARE
@ErrorMessage varchar(2000)
,@ErrorSeverity tinyint
,@ErrorState tinyint
/* Additional code */
BEGIN TRY
/* Your code here */
END TRY
BEGIN CATCH
SET @ErrorMessage = ERROR_MESSAGE()
SET @ErrorSeverity = ERROR_SEVERITY()
SET @ErrorState = ERROR_STATE()
RAISERROR(@ErrorMessage, @ErrorSeverity, @ErrorState)
BREAK
END CATCH
/* Further cleanup code */
Try/Catch blocks can be tricky but are much more thorough than @@error. More importantly, you can use the various error_xxx() functions within them. Here, I store the proper error message in variable @ErrorMessage, along with enough other data to re-raise the error. From here, any number of options are available; you could make @ErrorMessage an output variable, test for and handle specific errors, or build your own error messages (or adjust the existing ones to be clearer--you may get irritated finding out how often you'll want to do that). Other options will present themsleves.
Try/Catch 块可能很棘手，但比@@error 更彻底。更重要的是，您可以使用其中的各种 error_xxx() 函数。在这里，我将正确的错误消息存储在变量 @ErrorMessage 中，以及足够的其他数据来重新引发错误。从这里，可以使用任意数量的选项；您可以将 @ErrorMessage 设为输出变量，测试并处理特定错误，或构建您自己的错误消息（或调整现有的错误消息以使其更清晰——您可能会因为发现要这样做的频率而感到恼火）。其他选项将呈现它们自己的水平。
Something to look out for: in some situations, SQL will throw two error messages back to back... and error_message()will only catch the last one, which usually says something like "attempt to create object failed", with the real error given in the first error message. This is where building your own error message comes in.
需要注意的事情：在某些情况下，SQL 会连续抛出两个错误消息......并且error_message()只会捕获最后一个，它通常说“尝试创建对象失败”，真正的错误在第一条错误信息。这是构建您自己的错误消息的地方。
回答by Myles J
You could use a general Try/Catch and then construct more details about the error within the CATCH section e.g.
您可以使用通用的 Try/Catch，然后在 CATCH 部分构建有关错误的更多详细信息，例如
DECLARE @DetailedErrorDesc VARCHAR(MAX)
BEGIN TRY
--tsql code goes here
END TRY
BEGIN CATCH
SELECT @DetailedErrorDesc =
CAST(ERROR_NUMBER() AS VARCHAR) + ' : '+
CAST(ERROR_SEVERITY() AS VARCHAR) + ' : ' +
CAST(ERROR_STATE() AS VARCHAR) + ' : ' +
ERROR_PROCEDURE() + ' : ' +
ERROR_MESSAGE() + ' : ' +
CAST(ERROR_LINE() AS VARCHAR);
--Now you can decide what to do with the detailed error message....return it or log it etc
END CATCH
回答by Eduard Bader
use try ... catch and in catch block you can use ERROR_MESSAGE(), ERROR_LINE(), ERROR_PROCEDURE(), ERROR_STATE(), ERROR_SEVERITY(), ERROR_NUMBER() functions
使用 try ... catch 并在 catch 块中使用 ERROR_MESSAGE()、ERROR_LINE()、ERROR_PROCEDURE()、ERROR_STATE()、ERROR_SEVERITY()、ERROR_NUMBER() 函数
