Sql Server 2005错误处理 - 内部异常

In C# you can get the original error and trace the execution path (stack trace) using the inner exception that is passed up. I would like to know how this can be achieved using the error handling try/catch in sql server 2005 when an error occurs in a stored procedure nested 2 or 3 levels deep.

在C#中,您可以使用传递的内部异常获取原始错误并跟踪执行路径(堆栈跟踪)。我想知道如何在嵌套2或3级深度的存储过程中发生错误时使用sql server 2005中的错误处理try / catch来实现这一点。

I am hoping that functions like ERROR_MESSAGE(), ERROR_LINE(), ERROR_PROCEDURE(), ERROR_SEVERITY() can be easily passed up the line so that the top level stored proc can access them.

我希望像ERROR_MESSAGE(),ERROR_LINE(),ERROR_PROCEDURE(),ERROR_SEVERITY()这样的函数可以很容易地向上传递,以便*存储过程可以访问它们。

2 个解决方案

#1

The best way to handle this is using OUTPUT parameters and XML. The sample code below will demonstrate how and you can modify what you do with the XML in the TopProcedure to better handle your response to the error.

处理此问题的最佳方法是使用OUTPUT参数和XML。下面的示例代码将演示如何修改您在TopProcedure中对XML所做的操作,以便更好地处理您对错误的响应。

USE tempdb
go
CREATE PROCEDURE SubProcedure @RandomNumber int, @XMLErrors XML OUTPUT
AS
BEGIN
BEGIN TRY
    IF @RandomNumber > 50
        RaisError('Bad number set!',16,1)
    else
        select @RandomNumber
END TRY
BEGIN CATCH
    SET @XMLErrors = (SELECT * FROM (SELECT ERROR_MESSAGE() ErrorMessage, 
        ERROR_LINE() ErrorLine, ERROR_PROCEDURE() ErrorProcedure, 
        ERROR_SEVERITY() ErrorSeverity) a FOR XML AUTO, ELEMENTS, ROOT('root'))
END CATCH
END
go

CREATE PROCEDURE TopProcedure @RandomNumber int
AS
BEGIN
    declare @XMLErrors XML
    exec SubProcedure @RandomNumber, @XMLErrors OUTPUT
    IF @XMLErrors IS NOT NULL
        select @XMLErrors
END

go
exec TopProcedure 25
go
exec TopProcedure 55
go
DROP PROCEDURE TopProcedure
GO
DROP PROCEDURE SubProcedure
GO

The initial call to TopProcedure will return 25. The second will return an XML block that looks like this:

对TopProcedure的初始调用将返回25.第二个将返回一个如下所示的XML块:

<root>
  <a>
    <ErrorMessage>Bad number set!</ErrorMessage>
    <ErrorLine>6</ErrorLine>
    <ErrorProcedure>SubProcedure</ErrorProcedure>
    <ErrorSeverity>16</ErrorSeverity>
  </a>
</root>

Enjoy

#2

One way you could do this would be to create an in memory table and insert rows into it when you catch an exception. You would then re-raise the exception and the next function up the chain would then have a chance to handle the exception or also log the exception to the in memory table. It's nasty, but unfortunately there doesn't seem to be a way to get the T-SQL call stack :(

您可以这样做的一种方法是创建一个内存表,并在捕获异常时将行插入其中。然后,您将重新引发异常,然后链上的下一个函数将有机会处理异常或将异常记录到内存表中。这很讨厌,但遗憾的是似乎没有办法获得T-SQL调用堆栈:(

#1