← Writing

TRY…CATCH, ROLLBACK, THROW: the case for explicit error handling in T-SQL

2026-08-30

There is a minimalist school of T-SQL error handling, and it has a good pitch: put SET XACT_ABORT ON at the top of every procedure and you are done. Any runtime error dooms the transaction, rolls it back, aborts the batch, and propagates to the caller. No ceremony, no boilerplate, no CATCH blocks to get wrong.

I understand the appeal, and the setting itself is non-negotiable — it stays in every proc I write. But as the whole strategy, I have come to reject it, for a reason that has nothing to do with the engine and everything to do with the team: the minimalist proc hides its most important behaviour in a one-liner most readers cannot explain. Ask the next five people who touch your codebase what XACT_ABORT ON actually changes. In my experience two will come close, one will say "something about transactions," and two will admit they copy it because the template had it. The error path — the code that decides what your platform looks like at 2am — is invisible, implicit, and understood by almost nobody.

So my position is the opposite of the minimalist one: make the error path explicit. TRY…CATCH, a visible ROLLBACK, a THROW at the end — written out, every time, in every procedure that owns a transaction.

Explicit is a feature, not ceremony

The argument is the same one that applies to any code that matters: the procedure is read far more often than it is written, and it is read most urgently by someone under pressure — a reviewer approving the change, or an on-call engineer at 2am with a blocked fact table. For both readers, the explicit block answers the questions that matter without requiring folklore:

The minimalist counters that the engine already does all this. True — and invisible. XACT_ABORT behaviour is knowledge that lives in documentation and in the heads of the two people who read it; the explicit block is knowledge that lives in the code. On a team, code wins. A wall of procs that each spell out roll back, add context, re-throw is not noise; it is the error-handling policy of your platform, stated where everyone can see it, diffed when it changes, and enforced by the dumbest possible code review: "your CATCH block doesn't match the template."

There is a second, more practical argument: the explicit block is the only place certain work can happen. The moment a failure needs context — which load, which batch id, which watermark — or cleanup — releasing an sp_getapplock, marking a batch row failed — you need a CATCH block anyway. Adopting it everywhere means the procs that need it don't look structurally different from the procs that don't, and nobody bolts it on badly, later, under incident pressure.

The template

One shape, every procedure that owns a transaction:

CREATE PROCEDURE load.fact_positions @batch_id int
AS
SET NOCOUNT, XACT_ABORT ON;   -- the net; see below
 
BEGIN TRY
    BEGIN TRANSACTION;
 
    DELETE load.fact_positions_stage WHERE batch_id = @batch_id;
    INSERT INTO load.fact_positions_stage SELECT ...;
    UPDATE dim.instrument SET ...;
 
    COMMIT TRANSACTION;
END TRY
BEGIN CATCH
    IF XACT_STATE() <> 0 ROLLBACK TRANSACTION;
 
    DECLARE @msg nvarchar(2048) =
        CONCAT('fact_positions failed for batch ', @batch_id,
               ': ', ERROR_MESSAGE());
    THROW 50001, @msg, 1;
END CATCH

Every line of the CATCH block is doing a stated job. XACT_STATE() <> 0 rather than @@TRANCOUNT > 0, because inside a CATCH the transaction can be alive, absent, or doomed (XACT_STATE() = -1) — doomed transactions permit nothing but a rollback, and checking state handles all three cases in one guard. The CONCAT is where the error gains the context the engine cannot know: the batch, the watermark, the business identity of the run. And the THROW — not RAISERROR — re-raises with the enriched message while preserving a clean failure to the caller. RAISERROR mangles error numbers and, at default severity, aborts nothing; it is how procs end up "failing" while returning success. One syntactic trap worth naming: the statement before a bare THROW must have its semicolon, or the parser will spend twenty of your minutes teaching you so.

Yes, it is boilerplate. Boilerplate is a solved problem: put the template in a snippet, generate it if your procs are generated (the same metadata-driven machinery from the previous post can emit it), and let review enforce it. The cost is a dozen lines per proc; the return is that the error path of the entire estate reads identically.

THROW is the non-negotiable line

Whatever else the CATCH block does, it ends in THROW. This is the rule I hold hardest, because the most damaging pattern in warehouse T-SQL is the CATCH block that almost looks like the template:

BEGIN CATCH
    INSERT INTO etl.error_log (proc_name, error_message, logged_at)
    VALUES (OBJECT_NAME(@@PROCID), ERROR_MESSAGE(), SYSUTCDATETIME());
    -- no THROW
END CATCH

No re-throw. The proc swallows the failure, returns 0, and the Airflow task that called it goes green. The load is broken; every signal says it is fine. Downstream DAGs consume a fact table that silently stopped filling, while the error sits in etl.error_log — a table with no alerting, read for the first time three weeks later during the incident review, where it politely lists every date the platform lied about.

The orchestrator can only see what you throw. Airflow's retries, failure states, alert routes, and per-task history all key off the error crossing the connection boundary; every error handled privately is subtracted from all of them. Log if you must — then throw. A CATCH block that does not end in THROW is a bug until proven otherwise.

The corollary governs retries: they belong to the orchestrator, where attempts are visible, counted, and spaced by policy — not to WHILE loops with WAITFOR DELAY buried in procedures. I allow one exception: deadlock error 1205, retried two or three times in place, only when the whole procedure is idempotent, because the victim of a deadlock did nothing wrong and a task-level retry is a heavyweight answer to it.

Why XACT_ABORT stays underneath

Making the explicit case does not mean deleting the one-liner — the template keeps SET XACT_ABORT ON, and knowing why is what separates using the setting from cargo-culting it. TRY…CATCH has holes, and the setting covers exactly those holes:

So the two mechanisms are not competitors; they are layers. TRY…CATCH is the visible contract — rollback, context, re-throw, written where every reader can see it. XACT_ABORT is the net under the contract, for the failures that never reach your code at all. The minimalist school keeps the net and skips the contract; that works right up until a human has to read the proc.

One structural rule keeps the whole scheme simple: transactions stay flat and owned in exactly one place. BEGIN TRAN inside an open transaction just increments @@TRANCOUNT — the inner COMMIT commits nothing, any ROLLBACK rolls back everything, and the mismatch raises error 266 on the way out. A proc following the template should be the top-level owner of its transaction; procs it calls should not open their own.

The verdict

Should your warehouse procs use TRY…CATCH and ROLLBACK? Yes — as the visible, uniform contract for every procedure that owns a transaction: roll back on XACT_STATE(), enrich with the run's identity, re-throw with THROW. Keep SET XACT_ABORT ON underneath as the net for the failures TRY…CATCH cannot see, and understand that line rather than inheriting it. The engine could do most of this implicitly; your team cannot read the engine's mind at 2am. Write the error path down. The procs are read more often than they run — and when one fails, the CATCH block you spelled out is the difference between an incident report and an archaeology dig.

Share