尝试捕获TSQL -捕获而不是捕获

I have a stored procedure that seems not to be logging its errors correctly.

我有一个存储过程，似乎没有正确地记录它的错误。

The code is erroring, but the catch block doesn't seem to be coming into effect.

代码是错误的，但是catch块似乎没有生效。

The try block is fairly long - but the erroring section is simple and comes rightat the end, so I've precis'd that.

try块相当长——但是error部分很简单，并且在末尾处正确，所以我已经对它进行了概述。

BEGIN TRY 
insert into tbl_X
select * from #temp_tbl_Y

RETURN 1
END TRY

BEGIN CATCH
    Insert Into ExtractsErrorLog
    SELECT 
    getdate() as ErrorDate 
    ,object_name(@@procid) as ProcedureName
    ,ERROR_NUMBER() as ErrorNumber
    ,ERROR_LINE() as ErrorLine
    ,ERROR_MESSAGE() as ErrorMessage
    ;
DECLARE @errormessage as varchar(max);
DECLARE @errorseverity as int;
DECLARE @errorstate as int;

set @errormessage = ERROR_MESSAGE();
set @errorseverity = ERROR_SEVERITY();
set @errorstate = ERROR_STATE();

 RAISERROR (@errormessage,
            @errorseverity,
            @errorstate
               );


END CATCH;

The error the proc is failing on is our old friend "Column name or number of supplied values does not match table definition." I've fixed that error - It was a dumb lazy mistake - but I'm baffled why my error logging process didn't seem to be working - no row is being inserted into my ExtractsErrorLog table.

proc失败的错误是我们的老朋友“列名或提供的值数量与表定义不匹配”。我修正了这个错误——这是一个愚蠢的懒惰错误——但是我很困惑为什么我的错误日志记录过程没有工作——没有一行插入到我的ExtractsErrorLog表中。

2 个解决方案

#1

TSQL's TRY...CATCH does not catch that error. This error falls into the "compilation/recompilation" type errors that are not handled by the CATCH block "within the same level of execution".

TSQL试试……CATCH不能捕获错误。这个错误属于“编译/重新编译”类型错误，这些错误不是由CATCH块“在相同的执行级别”处理的。

From MSDN:

从MSDN:

The following types of errors are not handled by a CATCH block when they occur at the same level of execution as the TRY…CATCH construct:

当一个CATCH块发生与TRY. CATCH结构相同的执行级别时，不会处理以下类型的错误。

Compile errors, such as syntax errors, that prevent a batch from running.

编译错误，如语法错误，阻止批处理运行。

Errors that occur during statement-level recompilation, such as object name resolution errors that occur after compilation because of deferred name resolution

在语句级重新编译期间发生的错误，例如由于延迟的名称解析而在编译之后发生的对象名称解析错误

...

…

You can use TRY…CATCH to handle errors that occur during compilation or statement-level recompilation by executing the error-generating code in a separate batch within the TRY block. For example, you do this by placing the code in a stored procedure or by executing a dynamic Transact-SQL statement using sp_executesql. This allows TRY…CATCH to catch the error at a higher level of execution than the error occurrence. For example, the following code shows a stored procedure that generates an object name resolution error. The batch that contains the TRY…CATCH construct is executing at a higher level than the stored procedure; and the error, which occurs at a lower level, is caught.

您可以使用TRY. CATCH来处理在编译或语句级重新编译期间发生的错误，方法是在TRY块内的一个单独的批处理中执行产生错误的代码。例如，您可以将代码放在存储过程中，或者使用sp_executesql执行动态Transact-SQL语句。这允许TRY. CATCH在更高的执行级别捕获错误，而不是错误发生。例如，下面的代码显示了一个生成对象名称解析错误的存储过程。包含TRY. CATCH构造的批处理的执行级别高于存储过程;错误，发生在较低的水平，被捕获。

I ran into similar issues with a script creating a transaction inside a TRY...CATCH that would ROLLBACK the transaction if it failed. A statement inside the transaction was throwing that same error and caused the transaction to never be closed, as the CATCH was never entered.

我在尝试创建事务时遇到了类似的问题……CATCH如果失败，将回滚事务。事务内部的一个语句抛出了相同的错误，导致事务永远不会被关闭，因为捕获永远不会被输入。

As mentioned in the MSDN article, one alternative is to create a stored procedure out of your INSERT statement and then call that inside your try/catch. If the sproc is wrong, you'll catch the compilation error while trying to create it. If the table definition later changes to invalidate the sproc, then the TRY...CATCH will catch the exception for you.

正如MSDN文章中提到的，一种替代方法是在INSERT语句中创建一个存储过程，然后在try/catch中调用它。如果sproc是错误的，您将在尝试创建它时捕获编译错误。如果稍后的表定义更改为使sproc无效，则尝试…CATCH将会为你捕获异常。

If you want it to all live in one script, you can make it a temporary stored procedure, but you will then need to handle the compilation errors while you are creating the sprocs. It's not pretty, but it will work:

如果您希望它全部驻留在一个脚本中，您可以将它设置为一个临时存储过程，但是在创建sproc时需要处理编译错误。它不漂亮，但它会起作用:

-- Creating error sproc to re-use code
CREATE PROCEDURE #HandleError AS
    Insert Into ExtractsErrorLog
    SELECT  GETDATE() as ErrorDate 
            ,object_name(@@procid) as ProcedureName
            ,ERROR_NUMBER() as ErrorNumber
            ,ERROR_LINE() as ErrorLine
            ,ERROR_MESSAGE() as ErrorMessage;

    DECLARE @errormessage as varchar(max);
    DECLARE @errorseverity as int;
    DECLARE @errorstate as int;

    set @errormessage = ERROR_MESSAGE();
    set @errorseverity = ERROR_SEVERITY();
    set @errorstate = ERROR_STATE();

    RAISERROR ( @errormessage,
                @errorseverity,
                @errorstate);
GO

-- Create a stored procedure of our INSERT and catch any compilation errors
CREATE PROCEDURE #TEST AS
    insert into tbl_X
    select * from #temp_tbl_Y
GO
IF (@@ERROR <> 0) BEGIN
    exec #HandleError
    -- If there was an error creating the sprocs, don't continue to the next batch
    RETURN
END

-- If compilation succeeded, then run the sproc
BEGIN TRY 
    exec #TEST
    RETURN
END TRY
BEGIN CATCH
    exec #HandleError
END CATCH;

#2

-1

It's your RETURN: "Exits unconditionally from a query or procedure. RETURN is immediate and complete and can be used at any point to exit from a procedure, batch, or statement block."

这是您的返回:“无条件地退出查询或过程。返回是立即的和完整的，并且可以在任何时候用于从过程、批处理或语句块中退出。

#1