A CATCH block is not a guarantee that every failure reaches your procedure. Understanding what TRY CATCH cannot catch keeps transaction cleanup and caller handling honest.
Identify the Execution Boundary
TRY CATCH handles catchable runtime errors at its execution level and errors returned from lower levels. Same-scope compilation problems behave differently. The location of a failure matters as much as its error number.
I review execution scope when an expected CATCH message never appears. I also inspect the application response before deciding the database swallowed the error. A closed connection or canceled request changes which code can continue.
An error handler has no opportunity to run before its batch can compile. That explains why surrounding invalid syntax with TRY does not help. The engine must understand the handler before it can transfer execution there.
The missing message does not mean the command succeeded. Check the error sent to the client and the state of any transaction. Silence from your custom handler is a reason to inspect another boundary.
Use a disposable database and a dedicated connection for the safe demonstrations below. They intentionally raise errors and test cancellation. Keep unrelated transactions and unsaved work out of that connection.
A Syntax Error TRY CATCH Cannot Catch
The malformed statement below exists inside a string executed at a lower level. Its own TRY CATCH cannot handle the compile failure. The surrounding outer handler receives the error returned by sp_executesql.
BEGIN TRY
EXEC sys.sp_executesql N'
BEGIN TRY
SELECT FROM;
END TRY
BEGIN CATCH
SELECT N''Inner syntax handler'' AS HandlerLocation;
END CATCH;';
END TRY
BEGIN CATCH
SELECT N'Outer syntax handler' AS HandlerLocation,
ERROR_NUMBER() AS ErrorNumber,
ERROR_MESSAGE() AS ErrorMessage;
END CATCH;The outer batch remains valid SQL, so the demonstration can run without invalidating its outer handler. The inner string contains the intentional syntax error. Compare the reported handler location with the expected execution boundary.
Putting that malformed SELECT directly beside its same-scope TRY would stop compilation of the submitted batch. Its CATCH would never become an available runtime destination. Test that separately only if you want to inspect the raw client error.
Dynamic SQL does not repair invalid syntax. It moves compilation into another execution level that an outer handler can observe. Keep that distinction clear when deciding how to organize procedural code.
Missing Names TRY CATCH Cannot Catch Locally
A referenced table can disappear before execution, and deferred name resolution permits some procedure definitions to exist anyway. A statement-level compilation or name resolution failure can bypass a handler at the same level. Move the risky statement below the handling boundary.
BEGIN TRY
EXEC sys.sp_executesql N'
BEGIN TRY
SELECT ItemID FROM dbo.CatchScopeMissingTableDemo;
END TRY
BEGIN CATCH
SELECT N''Inner name handler'' AS HandlerLocation;
END CATCH;';
END TRY
BEGIN CATCH
SELECT N'Outer name handler' AS HandlerLocation,
ERROR_NUMBER() AS ErrorNumber,
ERROR_MESSAGE() AS ErrorMessage;
END CATCH;
GO
CREATE PROCEDURE dbo.CatchScopeInnerDemo
AS
BEGIN
SELECT ItemID FROM dbo.CatchScopeMissingTableDemo;
END;
GO
BEGIN TRY
EXEC dbo.CatchScopeInnerDemo;
END TRY
BEGIN CATCH
SELECT ERROR_NUMBER() AS ErrorNumber,
ERROR_MESSAGE() AS ErrorMessage,
ERROR_PROCEDURE() AS ErrorProcedure;
END CATCH;Confirm that the deliberately missing table does not exist in the test database. If it exists, the example tests a different condition. Give demonstration objects clearly isolated names and inspect their presence before running them.
The procedure example shows the outer caller catching a lower-level name error. Its definition belongs in its own batch, and the call follows GO. This is a useful boundary for operations whose objects are resolved during execution.
What TRY CATCH cannot catch depends partly on where the risky statement lives. An inner procedure or sp_executesql provides an outer handling opportunity. It still does not make disconnected code execute again.
Respect Connection-Ending Errors
Severity twenty and above indicate serious failures, but severity alone does not determine handler execution. If the connection survives, some such errors reach CATCH. If the connection ends, no code on that connection can finish its handler.
Do not manufacture a connection-ending server error on a shared instance to demonstrate this limit. Inspect documented message severities instead. An operational test that deliberately terminates connections requires an isolated environment and a separate failure test plan.
SELECT TOP (20) message_id, severity, is_event_logged, [text]
FROM sys.messages
WHERE language_id = 1033
AND severity >= 20
ORDER BY severity DESC, message_id;The catalog identifies message definitions rather than evidence that an error occurred. Read actual server diagnostics when investigating a real disconnection. Keep the reported error and connection state together in the incident record.
The application needs a clear response for lost connections. It cannot ask the disconnected session for an exception result set. Reconnection, transaction outcome ambiguity, and duplicate protection belong in that caller's design.
A Client Timeout TRY CATCH Cannot Catch
A client timeout sends cancellation rather than a normal catchable business error. The timeout control belongs to the client. In a dedicated SSMS window, set a short execution timeout before running the delayed test.
Set the timeout shorter than the deliberate five-second delay. The duration is a test input, not a measured performance result. Restore the window's timeout setting afterward so later queries use the intended policy.
SET XACT_ABORT ON;
BEGIN TRY
WAITFOR DELAY '00:00:05';
SELECT N'Delay completed' AS TestStatus;
END TRY
BEGIN CATCH
SELECT ERROR_NUMBER() AS ErrorNumber,
ERROR_MESSAGE() AS ErrorMessage;
END CATCH;A client timeout does not enter this CATCH block through the cancellation. SET XACT_ABORT ON is useful transaction protection, but it does not create a timeout handler. The application must still manage cancellation and connection cleanup explicitly.
Do not return a canceled connection to a pool without following the provider's cleanup policy. An open transaction can retain locks until it is rolled back or the session ends. Confirm the transaction outcome before retrying business work.
Keep Transaction Cleanup Explicit
Write transaction ownership into the procedure's contract. A procedure-owned transaction can roll back its failed unit of work in CATCH. An inherited transaction requires agreement with the caller about rollback and continuation.
Use XACT_STATE to distinguish no transaction, a committable transaction, and a doomed transaction. A transaction count alone does not identify whether commit is legal. Both state and ownership influence the cleanup path.
Error logging inside a doomed transaction also fails. Preserve the original error information and place durable logging at an appropriate boundary. A secondary logging error must not hide the original failure.
Test the Complete Failure Path
Which component reports the failure when the database handler cannot run? Exercise that path deliberately with invalid syntax, missing names, and a client cancellation. Keep connection-ending tests within their own isolated failure testing plan.
Document what TRY CATCH cannot catch beside the caller's responsibilities. Retain the original SQL error when it is available, and record connection loss separately. The distinction prevents a timeout from becoming an invented database success.
Finish by testing successful cleanup and successful normal execution. An error handler supports a complete operation rather than every possible server condition. Scope, transaction ownership, and caller behavior together determine whether that operation remains reliable.
Related reading on this blog: SET XACT_ABORT ON: Stopping Timeouts From Leaving Open Transactions and XACT_ABORT: Why a Half-Done Transaction Can Survive an Error.
A CATCH block is not universal insurance, it is one boundary in a complete failure path.
Published by Pinal Dave on SQLAuthority. More of my work at pinaldave.com.
