如何在SQL Server中从try/catch子句中获取内部错误?

I am running a stored procedure in SQL Server 2008 inside a try/catch. The stored procedure and the stored procs it calls raise a few errors but in the try/catch you only get the last error from the stored procedure that you are running.

我正在一个try/catch中运行SQL Server 2008中的一个存储过程。它调用的存储过程和存储过程会产生一些错误，但是在try/catch中，您只能从正在运行的存储过程中获得最后一个错误。

Is there a way/trick to be able to somehow catch ALL the errors generated by child stored proc calls while running a particular stored procedure? (assume that you have no access to any stored procedures so you can't modify where they can write the error, i.e. you can't just change all the stored procedures to stop raising errors and instead write them to some table and in your catch read from that table)

是否有一种方法/技巧可以在运行特定存储过程时捕获子存储proc调用生成的所有错误?(假设您无法访问任何存储过程，因此无法修改它们可以写入错误的位置，也就是说，您不能仅仅更改所有存储过程以停止产生错误，而是将它们写入某个表，并将其写入从该表读取的catch中)

3 个解决方案

#1

Here is a good resource for how to deal with error handling in SQL Server. http://www.sqlservercentral.com/articles/Development/anerrorhandlingtemplatefor2005/2295/

这里有一个关于如何处理SQL Server中的错误处理的好资源。http://www.sqlservercentral.com/articles/Development/anerrorhandlingtemplatefor2005/2295/

However, some of the methods require that you have the ability to change the code in order to capture the errors. There is really no way of getting around this. You can't just ignore the error, keep processing, and then come around later to deal with the error. In most, if not all, languages, exceptions have to be dealt with at the time the exception was raised. T-SQL is no different.

但是，有些方法要求您有能力更改代码以捕获错误。真的没有办法解决这个问题。你不能只是忽略错误，继续处理，然后稍后再来处理错误。在大多数(如果不是全部)语言中，异常必须在出现异常时处理。t - sql也不例外。

I personally use a stored procedure to log any error whenever it occurs. Here is what I use:

我个人使用一个存储过程来记录任何错误。我用的是:

CREATE PROCEDURE [dbo].[Error_Handler]
@returnMessage bit = 'False'
WITH EXEC AS CALLER
AS
BEGIN

  INSERT INTO Errors (Number,Severity,State,[Procedure],Line,[Message])
  VALUES (
    ERROR_NUMBER(),
    ERROR_SEVERITY(),
    ERROR_STATE(),
    isnull(ERROR_PROCEDURE(),'Ad-Hoc Query'),
    isnull(ERROR_LINE(),0),
    ERROR_MESSAGE())

  IF(@returnMessage = 'True')
  BEGIN
    select Number,Severity,State,[Procedure],Line,[Message]
    from Errors
    where ErrorID = scope_identity()
  END
END

#2

If you have stored procs that are raising more than one error, they need to be replaced no matter what. You probably have data integrity errors in your database. That is a critical, "everything needs to stop right now until this is fixed" kind of issue. If you can't replace them and they were incorrectly written to allow processing to continue when an error was reached, then I know of no way to find the errors. Errors are not recorded unless you tell them to be recorded. If the stored procs belong to a product you bought from another vendor and that's why you can't change them, your best bet is to change to a vendor that actually understands how to program database code because there is no salvaging a product written that badly.

如果您已经存储了导致多个错误的proc，那么无论如何都需要替换它们。您的数据库中可能有数据完整性错误。这是一个关键的问题，“一切都需要立即停止，直到这个问题得到解决”。如果您无法替换它们，并且它们被错误地编写为允许在到达错误时继续处理，那么我就知道无法找到错误。错误不会被记录，除非你告诉他们要被记录。如果存储的proc属于您从另一个供应商购买的产品，并且这就是为什么您不能更改它们的原因，那么您最好的选择是更改到一个供应商，该供应商实际上理解如何编程数据库代码，因为无法挽救编写得如此糟糕的产品。

#3

You wouldn't have a Java or c# methods raising error after error. Why do you expect SQL to allow this? An exception is an exception

您不会有Java或c#方法在错误之后引发错误。为什么您期望SQL允许这样做?例外是例外。

If the DB Engine is throwing errors then you have problems.

如果DB引擎抛出错误，那么就会出现问题。

What I've done before is to separate testing and checking code: find out what is wronf first and throw one exception If no errors, do your writes.

我以前所做的是分离测试和检查代码:首先找出什么是wronf，如果没有错误就抛出一个异常，请编写。

#1