sql - catch block 出错后如何继续？

Question

我需要循环遍历 10 条记录，并将这些记录插入到事务中的 3 个不同表中。我从带有事务的 while 循环中一次记录一个记录。

我的要求是，如果成功插入前 8 条记录，并且如果语句在第 9 条记录处失败，则应记录错误并继续插入第 10 条记录。

如果任何行有任何错误，存储过程不应停止。

我尝试将事务放在子 TRY-CATCH 块中，但仍然失败。

BEGIN TRY

DECLARE  @TotalRecord          AS INT = 10
        ,@LoopStartCount       AS INT = 1
        ,@AskPkQuotationId     AS INT;     

--Some select stuff here       
WHILE(@LoopStartCount <= @TotalRecord)
BEGIN 

    BEGIN TRY
    BEGIN TRAN

    --Do some insert update for each record.

    COMMIT TRAN
    END TRY

    BEGIN CATCH

    --Log Error and Continue with next record

    END CATCH
     SET @LoopStartCount = @LoopStartCount + 1;
    END



END TRY
BEGIN CATCH
 --Log parent error
END CATCH

Answer 1

仅对您的 While 循环实现 try...catch 而不是对您的整个代码。我在这里给了你一个示例演示。

DECLARE @I INT = 0
DECLARE @Table TABLE (
    ID INT identity(1, 1)
    ,c2 INT
    )

INSERT INTO @table (c2)
VALUES (1)
    ,(2)
    ,(0)
    ,(3)
    ,(4)

SELECT *
FROM @Table

WHILE @I < 5
BEGIN
    BEGIN TRY
        SET @I = @I + 1

        SELECT @I / (
                SELECT c2
                FROM @table
                WHERE ID = @I
                ) AS Quotient
    END TRY

    BEGIN CATCH
        SELECT ERROR_NUMBER() AS ErrorNumber
            ,ERROR_SEVERITY() AS ErrorSeverity
            ,ERROR_STATE() AS ErrorState
            ,ERROR_PROCEDURE() AS ErrorProcedure
            ,ERROR_LINE() AS ErrorLine
            ,ERROR_MESSAGE() AS ErrorMessage;
    END CATCH
END

此代码在第三行返回错误，但仍继续处理直到结束。