CODEWITHSUNDEEP
t-sql
Blocks
Blocks

Reputation: 351

Nested Try Catch how do I get to the Outer catch block?

How do I get to At "Outer catch block, delete any inserted records here?" When I create an error in both inner Try Catch blocks it still doesn't go to the Outer catch block. I have about 1000 records in a batch to post every minute and each record inserts into about 20 tables. So I need try all insert queries and not stop at the first error so I can get a detailed log and respond to all data errors quickly.

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 Error

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 Errors

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

Upvotes: 2

Views: 11665

Answers (2)

Hans Vonn
Hans Vonn

Reputation: 4089

I agree with @Alex that you should rollback the transaction for failures in your situation.

Here is an example of nested TRY - CATCH blocks:

-- 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

Upvotes: 5

Alex
Alex

Reputation: 276

Try using TRAN or TRANSACTION then you won't need nested try catches. ROLLBACK TRAN will undo any INSERT / UPDATE / DELETE...

Just remember that any logging you do will need to be done after the ROLLBACK TRAN or the log will be undone as well :)

BEGIN TRY
    PRINT 'At Try Block 1'
    BEGIN TRAN;
        INSERT INTO StudentDetails(Roll, [Name])
        VALUES('a', 'Abhijit')  -- Throwing Exception
    COMMIT TRAN;
END TRY
BEGIN CATCH
    PRINT 'At catch Block 1, Log Errors'
    ROLLBACK TRAN;
END CATCH
BEGIN TRY
    PRINT 'At Try Block 2'
    BEGIN TRAN;
        INSERT INTO StudentDetails(Roll, [Name])
        VALUES('a', 'Abhijit')  -- Throwing Exception
    COMMIT TRAN;
END TRY
BEGIN CATCH
    PRINT 'At catch Block 2, Log Errors'
    ROLLBACK TRAN;
END CATCH
--... to 20 inserts

Upvotes: 3

Related Questions