All 7 Error Handling with TRY CATCH questions
More in Error Handling with TRY CATCH
How do you combine a TRY CATCH block with a transaction to ensure the database is properly rolled back if an error occurs partway through? What is the difference between using THROW and RAISERROR to raise a custom error in SQL Server? What types of errors cannot be caught by a TRY CATCH block in SQL Server? How would you build a centralized, reusable error logging procedure that captures detailed error information whenever it is called from within a CATCH block?
Advertisement
SQL Server › Error Handling with TRY CATCH

How do nested TRY CATCH blocks work, and when would you use them in a complex stored procedure?

Advanced

Answer

A CATCH block can contain its own nested TRY CATCH block, letting you attempt a recovery action, like retrying an operation or performing partial cleanup, while still safely catching any new error that might occur during that recovery attempt itself, preventing an error inside your error handling code from crashing the entire procedure unexpectedly.
BEGIN TRY
  -- main operation
END TRY
BEGIN CATCH
  BEGIN TRY
    -- attempt cleanup or logging
    EXEC LogError;
  END TRY
  BEGIN CATCH
    PRINT 'Logging itself failed: ' + ERROR_MESSAGE();
  END CATCH
END CATCH;
Real-world example A critical financial procedure wraps its error logging call in its own nested TRY CATCH block, ensuring that even if the logging system itself has a problem, the original error handling process does not crash unexpectedly.

Common follow-ups: How deep can TRY CATCH blocks realistically be nested in SQL Server?;What are some real world scenarios where nested error handling like this becomes necessary?

Stored Procedures & Functions;Transactions & ACID
All 7 Error Handling with TRY CATCH questions