如何在存储过程中获取sql错误 [英] How to get sql error in stored procedure

问题描述

我使用的是 SQL Server 2005.我创建了一个大部分时间都可以工作的存储过程，但是我发现了一个实例，它不能执行我想要的操作.

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.

目前，代码做这样的事情

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

其中 @message_error 是一个输出变量.

Where @message_error is an output variable.

所以，我可以选择@@error 并获得一个数字，但我真正想要的是SQL 错误.

So, I can select @@error and get a number, but all I really want is the SQL error.

类似于嘿，我不能这样做，因为此列有 fk 约束 或其他什么.我在 msdn 上找到了这篇文章http://msdn.microsoft.com/en-us/library/ms178592(v=sql.90).aspx

Something like Hey, I couldn't do this because there is a fk constraint on this column or whatever. I found this article on msdn http://msdn.microsoft.com/en-us/library/ms178592(v=sql.90).aspx

但它只会通过 RAISERROR 抛出自定义异常，我不想创建自己的错误消息或异常，我只想知道为什么东西不起作用.我可以通过 Management Studio 执行存储过程并查看确切的 SQL 错误，但是尝试匹配来自站点的数据并以这种方式手动插入它很乏味.

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.

如何将 SQL 错误文本放入输出变量中?

How do I get the SQL error text into an output variable?

推荐答案

这是我使用的存储过程模板的一部分:

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 块可能很棘手，但比@@error 更彻底.更重要的是，您可以使用其中的各种 error_xxx() 函数.在这里，我将正确的错误消息存储在变量 @ErrorMessage 中，以及足够的其他数据来重新引发错误.从这里，可以使用任意数量的选项；您可以将 @ErrorMessage 设为输出变量，测试并处理特定错误，或构建您自己的错误消息(或调整现有的错误消息以使其更清晰——您可能会因为发现要这样做的频率而感到恼火).其他选项将各自呈现.

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.

需要注意的事情:在某些情况下，SQL 会连续抛出两条错误消息......而 error_message() 只会捕获最后一条，通常会说尝试创建对象失败"，第一个错误消息中给出了真正的错误.这就是构建您自己的错误消息的用武之地.

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错误的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！