tsql - 嵌套的 Try Catch 如何到达外部 catch block ？

Question

如何到达“外部 catch block ，删除此处插入的任何记录？”当我在两个内部 Try Catch block 中创建错误时，它仍然不会转到外部 catch block 。我每分钟有大约 1000 条记录要发布，每条记录插入大约 20 个表。因此，我需要尝试所有插入查询，而不是在出现第一个错误时停止，这样我可以获得详细的日志并快速响应所有数据错误。

CREATE TABLE [dbo].[StudentDetails](
    [Roll] [int] NULL,
    [Name] [varchar](50) NULL,
    [Address] [varchar](50) NULL
) ON [PRIMARY]

CREATE TABLE [dbo].[Library](
    [Roll] [int] NULL,
    [Name] [varchar](50) NULL,
    [Address] [varchar](50) NULL
) ON [PRIMARY]

--1 错误

BEGIN TRY
  print 'At Outer Try Block'
   BEGIN TRY
      print 'At Inner Try Block 1'
      INSERT INTO StudentDetails(Roll, [Name])
        VALUES('a', 'Abhijit')  -- Throwing Exception
   END TRY
   BEGIN CATCH
      print 'At Inner catch Block 1, Log Errors'
   END CATCH
   BEGIN TRY
      print 'At Inner Try Block 2'
      INSERT INTO StudentDetails(Roll, [Name])
        VALUES('1', 'Abhijit')  -- Throwing Exception
   END TRY
   BEGIN CATCH
      print 'At Inner catch Block 2, Log Errors'
   END CATCH
      --... to 20 inserts
END TRY
BEGIN CATCH
   print 'At Outer catch block, if an error delete any inserted records'
END CATCH

Query result
At Outer Try Block
At Inner Try Block 1
At Inner catch Block 1, Log Errors
At Inner Try Block 2

--2个错误

BEGIN TRY
  print 'At Outer Try Block'
   BEGIN TRY
      print 'At Inner Try Block 1'
      INSERT INTO StudentDetails(Roll, [Name])
        VALUES('a', 'Abhijit')  -- Throwing Exception
   END TRY
   BEGIN CATCH
      print 'At Inner catch Block 1, Log Errors'
   END CATCH
   BEGIN TRY
      print 'At Inner Try Block 2'
      INSERT INTO StudentDetails(Roll, [Name])
        VALUES('a', 'Abhijit')  -- Throwing Exception
   END TRY
   BEGIN CATCH
      print 'At Inner catch Block 2, Log Errors'
   END CATCH
      --... to 20 inserts
END TRY
BEGIN CATCH
   print 'At Outer catch block, if an error delete any inserted records'
END CATCH

Query result
At Outer Try Block
At Inner Try Block 1
At Inner catch Block 1, Log Errors
At Inner Try Block 2
At Inner catch Block 2, Log Errors

Answer 1

我同意@Alex 的观点，您应该在遇到失败时回滚事务。

下面是一个嵌套的 TRY - CATCH block 的例子:

-- Throw from inner catch to outer catch block
BEGIN TRY
    PRINT 'Outer Try';
    --Throw 50000, 'Outer Error', 1; -- Uncomment this to skip the inner try.

    BEGIN TRY
        PRINT 'Inner Try';
        Throw 50000, 'Inner Error', 1;
    END TRY
    BEGIN CATCH
        PRINT 'Inner Catch';
        Throw 50000, 'Inner Catch Error', 1;
    END CATCH

END TRY
BEGIN CATCH
    PRINT 'Outer Catch';
    PRINT ERROR_MESSAGE();
END CATCH

-- OUTPUT:
-- Outer Try
-- Inner Try
-- Inner Catch
-- Outer Catch
-- Inner Catch Error