The caller receives a cheerful success message after the procedure fails. An error handling template prevents that expensive misunderstanding. Roll back the work, record the failure, and let the original error reach the caller.
Give the Error Handling Template a Transaction Contract
I read transaction handling before trusting a procedure's catch block. A catch that hides the error leaves the application guessing. A catch that rolls back unrelated work creates another problem.
The template below owns its transaction. It rejects calls made inside an existing transaction. That explicit limitation keeps the rollback and logging behavior understandable.
Reusable procedures inside caller-owned transactions need another contract. Savepoints help only while the transaction remains committable. An uncommittable transaction still requires the owner's full rollback.
SET XACT_ABORT ON makes many runtime errors abort the transaction. TRY and CATCH provide the control flow around that failure. Neither setting catches every possible failure in every context.
Connection loss and some same-scope compile errors fall outside this pattern. Test the errors your application handles. Don't describe one template as protection against every interrupted execution.
Keep the Log Outside Rolled-Back Work
The error log needs to survive the failed business transaction. Insert its row after rolling back the owned transaction. Logging before rollback would lose the evidence with the business changes.
Create these objects in a disposable database. The demonstration balance table contains sample values. The constraint supplies a predictable way to force a failure.
The log includes error number, line and procedure. It also records the message and original login. Those fields help connect an application failure with the statement that raised it.
CREATE TABLE dbo.ProcedureErrorDemoLog
(
ErrorLogId bigint IDENTITY PRIMARY KEY,
LoggedAt datetime2(3) NOT NULL DEFAULT SYSUTCDATETIME(),
ErrorNumber int NOT NULL,
ErrorLine int NOT NULL,
ErrorProcedure nvarchar(128) NULL,
ErrorMessage nvarchar(4000) NOT NULL,
OriginalLoginName sysname NOT NULL
);
CREATE TABLE dbo.ProcedureBalanceDemo
(
AccountId int NOT NULL PRIMARY KEY,
BalanceAmount decimal(12,2) NOT NULL CHECK(BalanceAmount >= 0)
);
INSERT dbo.ProcedureBalanceDemo VALUES(1, 100.00);Use XACT_STATE to Decide the Cleanup
XACT_STATE returns zero when no transaction is active. One means an active transaction is committable. Minus one means an active transaction cannot commit.
This template rolls back whenever an owned transaction remains active. It doesn't attempt partial recovery after an uncommittable failure. That is the right choice for its all-or-nothing contract.
Capture the ERROR functions before entering any nested catch. A later logging error has its own error details. The original failure must remain the one sent back.
The procedure definition starts a separate batch. GO ends that definition before the tests. SSMS recognizes GO as a batch separator.
GO
CREATE PROCEDURE dbo.ApplyBalanceDemo
@AccountId int,
@Amount decimal(12,2)
AS
BEGIN
SET NOCOUNT ON;
SET XACT_ABORT ON;
IF @@TRANCOUNT <> 0
THROW 51000, 'This procedure requires no existing transaction.', 1;
BEGIN TRY
BEGIN TRANSACTION;
UPDATE dbo.ProcedureBalanceDemo
SET BalanceAmount = BalanceAmount + @Amount
WHERE AccountId = @AccountId;
IF @@ROWCOUNT <> 1
THROW 51001, 'The account does not exist.', 1;
COMMIT TRANSACTION;
END TRY
BEGIN CATCH
DECLARE @ErrorNumber int = ERROR_NUMBER(),
@ErrorLine int = ERROR_LINE(),
@ErrorProcedure nvarchar(128) = ERROR_PROCEDURE(),
@ErrorMessage nvarchar(4000) = ERROR_MESSAGE();
IF XACT_STATE() <> 0 ROLLBACK TRANSACTION;
BEGIN TRY
INSERT dbo.ProcedureErrorDemoLog
(ErrorNumber, ErrorLine, ErrorProcedure, ErrorMessage, OriginalLoginName)
VALUES(@ErrorNumber, @ErrorLine, @ErrorProcedure, @ErrorMessage, ORIGINAL_LOGIN());
END TRY
BEGIN CATCH
PRINT N'Error logging failed. The original error follows.';
END CATCH;
THROW;
END CATCH;
END;
GO
Preserve the Original Error With THROW
A bare THROW inside CATCH rethrows the original exception. Its number and original location remain useful to the caller. You don't need to reconstruct the message manually.
RAISERROR remains useful for some messaging patterns, but this catch needs faithful rethrowing. RAISERROR also doesn't honor XACT_ABORT in the same way. Rebuilding an exception can change its identity and location.
The error handling template uses a nested catch only around logging. That catch prevents a logging failure from replacing the business error. It doesn't prove the log row was written.
A production application needs a fallback when database logging fails. Record the returned exception in the application's own operational log. The database log cannot be its own only witness.
Force a Failure Through the Error Handling Template
The next call attempts to violate the nonnegative balance constraint. The outer test catch displays the returned exception. It lets the inspection queries run afterward.
Read the remaining balance and the log row. Check XACT_STATE and @@TRANCOUNT too. A failed call shouldn't leave this session carrying the template's open transaction.
On my test database the caller received error 547, the CHECK constraint conflict, not a generic message. The balance stayed at 100.00. The log held one row naming dbo.ApplyBalanceDemo, and both XACT_STATE and @@TRANCOUNT returned zero.
BEGIN TRY
EXEC dbo.ApplyBalanceDemo @AccountId = 1, @Amount = -200.00;
END TRY
BEGIN CATCH
SELECT ERROR_NUMBER() AS ReturnedErrorNumber,
ERROR_MESSAGE() AS ReturnedErrorMessage;
END CATCH;
SELECT AccountId, BalanceAmount FROM dbo.ProcedureBalanceDemo;
SELECT TOP (10) * FROM dbo.ProcedureErrorDemoLog ORDER BY ErrorLogId DESC;
SELECT XACT_STATE() AS TransactionState, @@TRANCOUNT AS TransactionDepth;Test the Error Handling Template on the Success Path
A failure demonstration alone misses half the contract. Execute a permitted amount and inspect the balance. Confirm the success path commits without adding an error row.
Also test a missing account identifier. That branch raises a deliberate business exception. It should follow the same cleanup and reporting rules as the constraint error.
I check both paths whenever a procedure template is copied. Copying code doesn't copy understanding. The catch block is a poor place for creative improvisation.
What should the application do when it receives the original error? A retry is correct only for a retryable failure. A violated business rule needs a different response.
Review the Logging Permissions
The execution identity needs permission to perform the business operation and log the failure. Test through the application's real execution context. Administrative testing hides missing grants.
Keep error messages and identifiers within the team's data handling rules. SQL text and messages can contain sensitive values. Restrict who reads the error table.
I don't add a commit inside CATCH to rescue partial work. The template promises one business operation or none. A failed statement doesn't renegotiate that promise.
An error handling template works when its ownership contract is clear. Document that contract beside the procedure. Then verify cleanup, logging and rethrowing through the caller's connection.
Keep the log insert short and independent of the failed business tables. A logging query that revisits blocked application rows creates another dependency during failure. Error reporting should need as little additional work as possible.
The original error line is relative to the failing module or batch. ERROR_PROCEDURE helps identify that scope. Save the deployed procedure definition when investigating a production exception.
Don't add a nested transaction around the log as a substitute for rollback. SQL Server nested transactions aren't independent durable units. A rollback of the outer transaction still removes their changes.
Also define retention for the error table. Repeated failures can accumulate sensitive messages and consume space. Restrict access and keep enough history for the operational investigation.
If a caller abandons the connection, the application needs its own record of that abandonment. Don't equate a missing database log row with success. Verify completion through the agreed business outcome.
Related reading on this blog: THROW or RAISERROR and XACT_ABORT: Why a Half-Done Transaction Can Survive an Error.
An error handler is not a place to hide failure, it is a contract for cleanup and honest reporting.
Published by Pinal Dave on SQLAuthority. More of my work at pinaldave.com.
