sql-server - 在存储过程中使用SQL Try Catch-我做对了吗？

Question

我有一个执行许多插入操作的存储过程，每个插入操作都会返回一个SCOPE_IDENTITY()主键值，然后将其用作后续插入操作的外键。

为了防止由于先前的插入操作失败而在没有FK的情况下执行插入操作，我将插入程序包装在try-catch块中，如下所示:

DECLARE @error INT;

BEGIN TRY
    insert statement here...
    SET @newId = SCOPE_IDENTITY();
END TRY
BEGIN CATCH
    -- assign error value to 
    SET @error = @@ERROR;
END CATCH

然后，下一个插入在继续之前检查错误var:

IF @error = 0
BEGIN
    BEGIN TRY
        next insert statement here using `@newId` from previous qry...
        SET @nextId = SCOPE_IDENTITY();
    END TRY
    BEGIN CATCH
        // assign error value to 
        SET @error = @@ERROR;
    END CATCH
END

等等。这是 Try-catch的合适应用还是过大？

Answer 1

这样做会很好，但是如果您的目的是在第一次失败后(即在包裹插入的插入中)根本不对过程进行任何其他工作，则可以:

仅使用一个try/catch块(并且流程将从第一个失败的插入语句跳转到catch块，并跳过所有后续的依赖于先前插入语句的插入)

如果出于某种原因更喜欢使用多个try/catches，则可以简单地从catch块重新引发异常(仅在SQL Server 2014及更高版本中有效/受支持)，或者在运行pre-katch之前从catch块返回Sql Server 2014(完成所有您想做的清理后)。

在2014年及之后的版本中，从catch块中使用THROW将重新引发异常并立即退出当前语句批处理(请参阅 here for details，如果在存储过程中使用，例如，这基本上意味着它将退出当前过程)。

作为在catch块(2014+)中使用THROW的简单示例:

if object_id('tempdb..#TestRethrow') is not null
    drop table #TestRethrow;

CREATE TABLE #TestRethrow(ID INT PRIMARY KEY);

BEGIN TRY
    INSERT #TestRethrow(ID) VALUES(1);
END TRY
BEGIN CATCH
    PRINT 'In catch block 1 - SHOULD NOT FIRE.';
    THROW;
END CATCH;

print 'between catch blocks';

BEGIN TRY
    -- Unique key violation
    INSERT #TestRethrow(ID) VALUES(1);
END TRY
BEGIN CATCH
    PRINT 'In catch block 2 - WILL FIRE';
    THROW;
    PRINT 'At end of catch block 2 - SHOULD NOT FIRE';
END CATCH;

print 'out of catch block 2 - SHOULD NOT FIRE'
select 1;

go

print 'SHOULD FIRE - Another batch (assuming you are using SSMS for example, or another client that separates batches using GO';
go

如果使用的是SQL 2012或更早版本，则可以使用return代替throw，但是您可能想先做一些异常(exception)处理(例如，将其记录下来，或者使用较旧的RAISERROR语法将其引发):

    if object_id('tempdb..#TestRethrow') is not null
    drop table #TestRethrow;

CREATE TABLE #TestRethrow(ID INT PRIMARY KEY);

BEGIN TRY
    INSERT #TestRethrow(ID) VALUES(1);
END TRY
BEGIN CATCH
    PRINT 'In catch block 1 - SHOULD NOT FIRE.';
    return;
END CATCH;

print 'between catch blocks';

BEGIN TRY
    -- Unique key violation
    INSERT #TestRethrow(ID) VALUES(1);
END TRY
BEGIN CATCH
    PRINT 'In catch block 2 - WILL FIRE';
    return
    PRINT 'At end of catch block 2 - SHOULD NOT FIRE';
END CATCH;

print 'out of catch block 2 - SHOULD NOT FIRE'
select 1;

go

print 'SHOULD FIRE - Another batch (assuming you are using SSMS for example, or another client that separates batches using GO';
go