The procedure has a BEGIN TRY. It has a BEGIN CATCH. It has IF @@TRANCOUNT > 0 ROLLBACK TRANSACTION. It has been in production for three years. And last Tuesday it wrote half an order, returned success to the application, and nobody found out until the month-end reconciliation came up 612 orders short.
Every line of that procedure came from a working example, and the examples leave things out. They show the path where an error fires, the CATCH runs, the rollback happens, and everyone goes home. They skip the cases where the CATCH never fires, where the rollback runs but the caller still thinks the batch succeeded, and where @@TRANCOUNT says 1 and the transaction is already dead.
TRY...CATCH itself shipped in SQL Server 2005, but the code below runs on SQL Server 2008 and later, because the samples initialize variables inside DECLARE and that syntax arrived in 2008. On 2005, split every DECLARE @x TYPE = expr; into a DECLARE and a SET. Everything here works on every edition. None of it is Enterprise-only.
The shape everyone copies
BEGIN TRY
BEGIN TRANSACTION;
INSERT dbo.Invoice (InvoiceID, CustomerID, Amount)
VALUES (1, 42, 100.00);
INSERT dbo.Invoice (InvoiceID, CustomerID, Amount)
VALUES (1, 42, 100.00); -- primary key violation
COMMIT TRANSACTION;
END TRY
BEGIN CATCH
IF @@TRANCOUNT > 0
ROLLBACK TRANSACTION;
SELECT ERROR_NUMBER() AS error_number,
ERROR_MESSAGE() AS error_message;
END CATCH;
The rollback is correct. The SELECT is the bug. A client running this gets a result set, not an exception. ADO.NET raises no SqlException, the retry logic never engages, and the caller commits its own outer work believing this one succeeded. The error was caught and then discarded.
Two fixes go in this block. The CATCH has to end by re-raising, and the @@TRANCOUNT test has to become an XACT_STATE() test. The rest of the article is why.
What TRY...CATCH does not catch
Microsoft lists the exclusions on the TRY...CATCH page. Read them as a list of production incidents.
Warnings and informational messages at severity 10 or lower. Nothing fires and the TRY block carries on.
BEGIN TRY
RAISERROR ('Row count looks wrong.', 10, 1);
PRINT 'the TRY block kept running';
END TRY
BEGIN CATCH
PRINT 'this never prints';
END CATCH;
Errors at severity 20 and above that stop task processing for the session. The connection closes and no session remains to run a CATCH block. Microsoft adds a qualifier: if a severity 20 or higher error occurs and the connection is not disrupted, TRY...CATCH does handle it.
Attentions. A client cancel, a broken connection, or a CommandTimeout firing in .NET sends an attention. The batch stops where it stands, the CATCH does not run, and the open transaction stays open. Microsoft's post on attention events shows @@TRANCOUNT returning 1 after a cancelled batch with the exclusive locks still held, blocking everything behind it until someone commits or rolls back on that connection, the connection closes, or the pool resets it. That is the most common way a TRY/CATCH procedure leaves a transaction on the floor, and SET XACT_ABORT ON is the documented mitigation.
KILL. A system administrator ending the session is not an error you get to handle.
Compile errors in the same batch. A syntax error stops the batch from running at all, so there is no CATCH to enter.
BEGIN TRY
SELECT * FROM dbo.Invoice WHERE;
END TRY
BEGIN CATCH
PRINT 'this never prints';
END CATCH;
Object name resolution errors, and errors raised during statement-level recompilation, including the ones deferred name resolution produces. Microsoft lists those as separate items in the same-level group, and the difference between them is worth its own section.
Object name resolution: the one that looks like a bug
Run this against any database:
PRINT 'before';
BEGIN TRY
SELECT * FROM dbo.NoSuchTable;
END TRY
BEGIN CATCH
PRINT 'caught: ' + ERROR_MESSAGE();
END CATCH;
You get Msg 208, Level 16, Invalid object name 'dbo.NoSuchTable', no caught: line, and no before line either. Severity 16 sits inside the range TRY/CATCH handles, and the CATCH was right there. The reason is execution level. SQL Server binds the object name while it compiles the batch, the binding fails, and the batch never runs, so the TRY block and the CATCH block are both stillborn.
Deferred name resolution proper is the stored procedure case, and it bites the same way for a different reason:
CREATE OR ALTER PROCEDURE dbo.ReadMissingTable_Inner
AS
BEGIN
BEGIN TRY
SELECT * FROM dbo.NoSuchTable;
END TRY
BEGIN CATCH
PRINT 'caught: ' + ERROR_MESSAGE();
END CATCH;
END;
GO
EXEC dbo.ReadMissingTable_Inner;
The CREATE succeeds, because SQL Server compiles the procedure without resolving dbo.NoSuchTable. That is the deferred part. At execution the missing table surfaces during statement-level recompilation, at the same execution level as the TRY...CATCH wrapped around it, and the CATCH does not fire. Microsoft names this exclusion in its own words: errors that occur during statement-level recompilation, such as object name resolution errors that occur after compilation because of deferred name resolution.
Move the handler one level up and it works:
CREATE OR ALTER PROCEDURE dbo.ReadMissingTable
AS
BEGIN
SELECT * FROM dbo.NoSuchTable;
END;
GO
BEGIN TRY
EXEC dbo.ReadMissingTable;
END TRY
BEGIN CATCH
PRINT 'caught: ' + ERROR_MESSAGE();
END CATCH;
Now caught: Invalid object name 'dbo.NoSuchTable'. prints. Microsoft states the rule: an error during compilation or statement-level recompilation at a lower execution level, such as inside sp_executesql or a user-defined stored procedure, occurs below the TRY...CATCH construct, and the associated CATCH block handles it. Swapping the EXEC dbo.ReadMissingTable for EXEC sp_executesql N'SELECT * FROM dbo.NoSuchTable;' gives the same result.
Two consequences. A deployment script that creates a table and then queries it in the same batch cannot protect the query with TRY/CATCH, so split the batch with GO or push the query one level down. And a procedure cannot catch its own missing objects, so the handler for a bad deployment belongs in the caller. CREATE OR ALTER PROCEDURE needs SQL Server 2016 SP1 or later; on anything older, use the DROP/CREATE pair.
Why @@TRANCOUNT > 0 is not enough
Two tables with a foreign key:
CREATE TABLE dbo.Parent (ParentID INT NOT NULL PRIMARY KEY);
CREATE TABLE dbo.Child
(
ChildID INT NOT NULL PRIMARY KEY,
ParentID INT NOT NULL REFERENCES dbo.Parent (ParentID)
);
INSERT dbo.Parent (ParentID) VALUES (1);
GO
Run the same failure twice, once with XACT_ABORT off and once with it on:
SET XACT_ABORT OFF;
BEGIN TRY
BEGIN TRANSACTION;
INSERT dbo.Child (ChildID, ParentID) VALUES (1, 1);
INSERT dbo.Child (ChildID, ParentID) VALUES (2, 999); -- FK violation
COMMIT TRANSACTION;
END TRY
BEGIN CATCH
SELECT @@TRANCOUNT AS trancount, XACT_STATE() AS xact_state;
IF XACT_STATE() <> 0 ROLLBACK TRANSACTION;
END CATCH;
Both read 1, which is the XACT_ABORT OFF behavior Microsoft documents: in some cases only the statement that raised the error rolls back and the transaction continues. The transaction is alive, holds the row ChildID = 1, and will commit if you tell it to. A CATCH block that tests @@TRANCOUNT > 0 and then picks between commit and rollback on a business condition commits half a unit of work here with no warning.
Change SET XACT_ABORT OFF to SET XACT_ABORT ON and run the identical block. Now trancount is 1 and xact_state is -1. Microsoft's XACT_STATE page documents this exact pairing: SET XACT_ABORT ON renders the transaction uncommittable when the constraint violation occurs. Same error, same @@TRANCOUNT, opposite meaning.
XACT_STATE() returns three values:
| Value | Meaning |
|---|---|
| 1 | An active user transaction exists. The session can write data and commit. |
| 0 | No active user transaction exists for the session. |
| -1 | An active user transaction exists, but an error classified it as uncommittable. The session cannot commit and cannot roll back to a savepoint. It can only request a full rollback. Reads are allowed; writes are not. |
Microsoft states the distinction: @@TRANCOUNT detects whether an active user transaction exists but cannot determine whether that transaction is uncommittable. That is the argument for XACT_STATE() in a CATCH block.
A transaction becomes uncommittable because an error that would end a transaction outside a TRY block dooms it instead when it fires inside one. Write to it and you get Msg 3930, The current transaction cannot be committed and cannot support operations that write to the log file. Roll back the transaction. Leave it and return to the caller, and the engine rolls back any active uncommittable transaction when the outermost batch finishes. If no error message went out when the transaction entered that state, the client receives one at batch completion telling it an uncommittable transaction was detected and rolled back.
Until that rollback happens, the transaction is still the oldest active one on the instance, and an uncommittable transaction holds the log open the same way a forgotten BEGIN TRAN does. A CATCH block that returns without rolling back is a log-growth incident with a delay on it.
SET XACT_ABORT ON
The Remarks on Microsoft's SET XACT_ABORT page run to seven lines and every one of them matters. With XACT_ABORT ON, a T-SQL statement that raises a run-time error terminates and rolls back the entire transaction. With it OFF, in some cases only the failing statement rolls back and the transaction continues, and depending on severity the whole transaction may roll back anyway. OFF is the default in a T-SQL statement; ON is the default in a trigger. Compile errors are not affected. The setting takes effect at execute time, not parse time. XACT_ABORT must be ON for data modification statements inside an implicit or explicit transaction against most OLE DB providers, including SQL Server, unless the provider supports nested transactions. And with ANSI_WARNINGS OFF, permissions violations abort transactions.
Why it belongs in every procedure that writes: without it, the state of your transaction after an error depends on which error you got. Some errors abort the statement, some abort the batch, some doom the transaction, and the CATCH has to be correct across all three. With XACT_ABORT ON, a run-time error has one outcome: the transaction is doomed, and the CATCH has one job.
Two exceptions stay outside that. Compile errors ignore the setting, as the Remarks say. And RAISERROR does not honor SET XACT_ABORT, which Microsoft states on both the RAISERROR page and the THROW comparison table, so an error you raise yourself with RAISERROR inside a TRY block leaves the transaction committable and your CATCH back to making a decision. THROW does honor it.
Name the cost too. XACT_ABORT ON removes the option of continuing after a recoverable error inside the transaction. If you are looping over a staging table and want to skip bad rows while committing the good ones, XACT_ABORT ON kills that design. Validate the rows before the transaction opens, or run each row in its own transaction.
Rethrowing: THROW versus RAISERROR
A CATCH block that does not re-raise lies to its caller. The question is which statement to re-raise with.
RAISERROR came first and is what most published templates still use:
CREATE OR ALTER PROCEDURE dbo.Rethrow_Raiserror
AS
BEGIN
BEGIN TRY
SELECT 1 / 0 AS result;
END TRY
BEGIN CATCH
DECLARE @msg NVARCHAR(2048) = ERROR_MESSAGE(),
@sev INT = ERROR_SEVERITY(),
@state INT = ERROR_STATE();
RAISERROR (@msg, @sev, @state);
END CATCH;
END;
GO
EXEC dbo.Rethrow_Raiserror;
The caller sees Msg 50000. The original divide-by-zero was Msg 8134. Any upstream handler that branches on error number now sees 50000 for this error, 50000 for a deadlock, and 50000 for a constraint violation, and the line number points at the RAISERROR rather than the SELECT.
That is a documented limit. RAISERROR with a msg_id requires that ID to exist in sys.messages, added through sp_addmessage, and the sp_addmessage page puts the hard floor on user-defined numbers: an integer between 50,001 and 2,147,483,647. RAISERROR cannot raise a system error number. Pass a message string instead of an ID and it always raises 50000.
It has a second failure mode. RAISERROR treats msg_str as a printf format string, and plenty of system messages contain a literal %. Feed one of those back through RAISERROR (@msg, ...) and the message comes out mangled or the re-raise errors.
A third shows up under an application login. Severity levels 19 through 25 can only be specified by a member of the sysadmin fixed server role or a principal with ALTER TRACE, and they require WITH LOG. Severity 19 sits inside the range TRY/CATCH catches, so RAISERROR (@msg, @sev, @state) handles the severity 16 errors you tested with and hits that wall the first time a severity 19 error reaches it.
THROW arrived in SQL Server 2012:
CREATE OR ALTER PROCEDURE dbo.Rethrow_Throw
AS
BEGIN
BEGIN TRY
SELECT 1 / 0 AS result;
END TRY
BEGIN CATCH
THROW;
END CATCH;
END;
GO
EXEC dbo.Rethrow_Throw;
The caller sees Msg 8134, Level 16, State 1, Divide by zero error encountered. with the line number of the SELECT inside the procedure. Parameterless THROW re-raises the caught exception with its original number, severity, state, and line. It takes no message string, so no printf parsing mangles a %, and no severity parameter, so the sysadmin/ALTER TRACE restriction that governs RAISERROR has nothing to attach to. Microsoft documents no permission requirement for THROW, though it never addresses the severity 19 rethrow case in so many words.
Microsoft's comparison table adds three more differences: THROW needs error_number defined nowhere; THROW does not accept printf formatting; and THROW honors SET XACT_ABORT and rolls the transaction back when it is ON, which RAISERROR never does.
Two rules. Parameterless THROW has to sit inside a CATCH block. And the statement before THROW must end with a semicolon. Microsoft states the rule without the reason; Erland Sommarskog supplies it in Error and Transaction Handling in SQL Server, Part 2. ROLLBACK TRANSACTION accepts an identifier argument, so ROLLBACK TRANSACTION THROW parses as a request to roll back a transaction named THROW and comes back with Cannot roll back THROW. No transaction or savepoint of that name was found.
For a custom message with a stable number, THROW takes an error_number between 50000 and 2147483647 with no sys.messages entry required, as in THROW 50001, N'Payment exceeds the invoice balance.', 1;. Anything below 50000 is rejected, so THROW 8134, ... cannot fake a system error either. Parameterless THROW is the only statement that preserves one.
Nested blocks, nested transactions, and savepoints
TRY...CATCH nests without surprises. A CATCH block can hold its own TRY...CATCH, an error inside a nested TRY passes control to the nested CATCH, and without one an error inside a CATCH returns to the caller like any other error.
Transactions break expectations. BEGIN TRANSACTION increments @@TRANCOUNT by 1, COMMIT TRANSACTION decrements it by 1, ROLLBACK TRANSACTION without a savepoint name rolls back to the outermost BEGIN TRANSACTION and sets @@TRANCOUNT to 0, and ROLLBACK TRANSACTION savepoint_name leaves @@TRANCOUNT alone.
An inner COMMIT commits nothing. It decrements a counter. An inner ROLLBACK destroys work the outer caller did before it called you.
The standard bug: dbo.Inner opens its own transaction, catches an error, rolls back, and returns. dbo.Outer called it inside its own transaction. @@TRANCOUNT is now 0, dbo.Outer has lost work it never knew about, and its COMMIT TRANSACTION fails with The COMMIT TRANSACTION request has no corresponding BEGIN TRANSACTION. Microsoft notes that a ROLLBACK TRANSACTION leaving @@TRANCOUNT different at procedure exit than at entry produces an informational message, which nobody reads.
Savepoints are the documented way out, with one limitation to code around. From Microsoft's SAVE TRANSACTION page:
If the transaction is uncommitable, a rollback to the savepoint is not allowed because the savepoint rollback writes to the log. Just return to the caller, which should roll back the outer transaction.
Microsoft's page spells it with one t; the quote stands as written there.
Microsoft's example guards it with IF XACT_STATE() <> -1 ROLLBACK TRANSACTION ProcedureSave;. SAVE TRANSACTION is also unsupported in distributed transactions, whether started with BEGIN DISTRIBUTED TRANSACTION or promoted from a local one.
That limitation collides with SET XACT_ABORT ON, which dooms the transaction on any run-time error. Turn both on and the savepoint is unreachable at the moment you want it. Pick one:
- All-or-nothing writes:
SET XACT_ABORT ON, the procedure opens its own transaction only when@@TRANCOUNT = 0, and the CATCH does a full rollback. This covers most procedures. - Partial rollback inside a caller's transaction:
XACT_ABORToff, a savepoint, and a CATCH that checksXACT_STATE()before rolling back to it and returns to the caller when the transaction is doomed.
Logging that survives the rollback
The rollback that saves your data also erases the row you wrote to dbo.ErrorLog. Two rules keep the log.
First, do the logging insert after the rollback. An uncommittable transaction permits reads and a ROLLBACK TRANSACTION and nothing else, so an INSERT into your log table while XACT_STATE() is -1 raises 3930 on top of the error you wanted to record. The ERROR_* functions stay valid for the whole scope of the CATCH block, including after the rollback, so copy them into local variables first.
Second, for anything accumulated during the TRY block, use a table variable. Microsoft states the rule: because table variables have limited scope and are not part of the persistent database, transaction rollbacks do not affect them. That is the one clean way to carry per-row rejection detail across a rollback.
Fill the table variable before the transaction opens, or at least before the error. Microsoft does not document whether a write to a table variable is permitted while XACT_STATE() is -1, and the general rule is that a doomed transaction allows reads and a rollback and nothing else. Design for the documented rule.
A staging load makes the shape concrete. dbo.Stage_Order holds 2.4 million rows, and 1,847 of them carry a ShipDate string that will not convert.
DECLARE @rejects TABLE
(
StageRowID BIGINT NOT NULL,
RejectReason NVARCHAR(200) NOT NULL
);
SET XACT_ABORT ON;
BEGIN TRY
INSERT @rejects (StageRowID, RejectReason)
SELECT s.StageRowID,
LEFT(N'ShipDate is not a valid date: ' + s.ShipDate, 200)
FROM dbo.Stage_Order AS s
WHERE s.ShipDate IS NOT NULL
AND TRY_CONVERT(DATE, s.ShipDate, 101) IS NULL;
BEGIN TRANSACTION;
INSERT dbo.OrderHeader (StageRowID, CustomerID, ShipDate)
SELECT s.StageRowID, s.CustomerID, TRY_CONVERT(DATE, s.ShipDate, 101)
FROM dbo.Stage_Order AS s
WHERE NOT EXISTS (SELECT 1 FROM @rejects AS r
WHERE r.StageRowID = s.StageRowID);
COMMIT TRANSACTION;
END TRY
BEGIN CATCH
IF XACT_STATE() <> 0
ROLLBACK TRANSACTION;
INSERT dbo.LoadReject (StageRowID, RejectReason, LoggedAt)
SELECT StageRowID, RejectReason, SYSUTCDATETIME() FROM @rejects;
THROW;
END CATCH;
The 1,847 reject rows survive the rollback because they live in @rejects. The insert into dbo.LoadReject runs after the rollback, when the transaction is gone and writes are legal again. THROW still reaches the caller.
Catch conversion errors with TRY_CONVERT instead
A CATCH block that catches Msg 241, Conversion failed when converting date and/or time from character string tells you a conversion failed somewhere in 2.4 million rows. It does not name the row, and the whole batch died to deliver that.
TRY_CONVERT, TRY_CAST, and TRY_PARSE all arrived in SQL Server 2012. Each returns NULL where the conversion fails, which turns a dead batch into a WHERE clause that names the bad rows. Only TRY_CONVERT is a reserved keyword, and only from compatibility level 110; Microsoft says of the other one that TRY_CAST "isn't a new reserved keyword and is available in all compatibility levels."
One trap: request a conversion SQL Server does not permit and TRY_CAST and TRY_CONVERT raise an error rather than returning NULL.
SELECT TRY_CAST(4 AS XML) AS result;
-- Msg 529: Explicit conversion from data type int to xml is not allowed.
NULL means the value did not fit the target type. An error means the pair of types cannot be converted at any value. Confirm the conversion is legal before you lean on the NULL.
Catching the conversion is the second question anyway. The first is why the conversion failed in the first place, which in most loads is a column typed as VARCHAR holding something that was never a string.
TRY_PARSE is a different tool. It handles string to date/time and number types only, it depends on the .NET Framework CLR, it cannot be remoted for that reason, and Microsoft warns of parsing overhead. Use it when you need culture-aware parsing with USING, and TRY_CONVERT the rest of the time.
The template
CREATE OR ALTER PROCEDURE dbo.ApplyPayment
@InvoiceID INT,
@Amount DECIMAL(19,4)
AS
BEGIN
SET NOCOUNT, XACT_ABORT ON;
DECLARE @own_transaction BIT = CASE WHEN @@TRANCOUNT = 0 THEN 1 ELSE 0 END;
BEGIN TRY
IF @own_transaction = 1
BEGIN TRANSACTION;
UPDATE dbo.Invoice
SET Balance = Balance - @Amount
WHERE InvoiceID = @InvoiceID;
IF @@ROWCOUNT = 0
THROW 50010, N'Invoice not found.', 1;
INSERT dbo.Payment (InvoiceID, Amount, PaidAt)
VALUES (@InvoiceID, @Amount, SYSUTCDATETIME());
IF @own_transaction = 1
COMMIT TRANSACTION;
END TRY
BEGIN CATCH
DECLARE @error_number INT = ERROR_NUMBER(),
@error_severity INT = ERROR_SEVERITY(),
@error_state INT = ERROR_STATE(),
@error_procedure SYSNAME = ERROR_PROCEDURE(),
@error_line INT = ERROR_LINE(),
@error_message NVARCHAR(4000) = ERROR_MESSAGE();
IF XACT_STATE() <> 0 AND @own_transaction = 1
ROLLBACK TRANSACTION;
IF XACT_STATE() = 0
INSERT dbo.ErrorLog
(ErrorNumber, ErrorSeverity, ErrorState,
ErrorProcedure, ErrorLine, ErrorMessage, LoggedAt)
VALUES
(@error_number, @error_severity, @error_state,
@error_procedure, @error_line, @error_message, SYSUTCDATETIME());
THROW;
END CATCH;
END;
SET NOCOUNT, XACT_ABORT ON dooms the transaction on a run-time error and keeps row-count messages away from chatty clients. @own_transaction stops the procedure committing or rolling back a transaction it did not open. The ERROR_* values go into local variables first, because those functions return NULL outside the CATCH scope. The rollback runs when a transaction is still open and this procedure owns it, doomed or not. The log insert runs when XACT_STATE() is 0, so no transaction remains to poison the write. THROW ends the block, and the caller gets the original number, severity, and line.
When a caller holds the transaction, this procedure logs nothing and re-throws, whether the error doomed the transaction or left it committable. The transaction's owner rolls back and logs, and an error logged twice by two procedures is worse than logged once. Name the consequence before you ship it: a caller with no CATCH block of its own loses the log entry. If your callers cannot be trusted with one, collect the error into a table variable and write it out from whichever procedure owns the rollback.
Two workloads land in this template more than any other. One is the upsert this template usually wraps, where the MERGE brings its own concurrency failures and the CATCH decides whether a deadlock gets retried or re-thrown. The other is error handling around a long batched delete, where each batch commits on its own and the template's job is to stop batch 400 of 900 from rolling back the 399 that already succeeded. Both want XACT_ABORT ON and a transaction that spans one batch, never the whole loop.
The order to work in when you write the CATCH block
SET NOCOUNT, XACT_ABORT ONat the top of every procedure that writes.- Decide whether the procedure owns its transaction. Compare
@@TRANCOUNTto 0 at entry and record the answer. - Put the error-prone work inside
BEGIN TRY. Keep the transaction as short as the work allows. - In the CATCH, copy the six
ERROR_*values into local variables first. - Test
XACT_STATE(), not@@TRANCOUNT, before rolling back. - Log after the rollback, from those local variables, with a table variable for anything collected during the TRY block.
- End the CATCH with
THROW;on SQL Server 2012 and later. On 2008 and 2008 R2, re-raise withRAISERROR, write the originalERROR_NUMBER()into the message text because the caller will only see 50000, and escape any%in the message before you pass it back. - Move conversion-error handling into a
WHEREclause withTRY_CONVERTorTRY_CAST. - Split any batch that creates an object and then references it, so deferred name resolution does not escape the CATCH.
- Set the client command timeout above the longest expected run, and have the application issue
IF @@TRANCOUNT > 0 ROLLBACKon its own error path. Attentions are the one failure TRY/CATCH cannot help with.
If you would rather hand this off, it is the kind of work I do. See SQL Server DBA services, review client results, or get in touch.