Error handling fails when the code checks yesterday's value. Old @@ERROR checks lose the original failure as soon as another statement runs.
Why @@ERROR Checks Miss the Failure
The global value describes the error from the most recently executed statement. A successful statement sets it back to zero. Logging, assignment, and condition checks can therefore erase the failure you intended to inspect.
I look for statements between the failing operation and its error check. I also trace the caller's interpretation of the procedure result. A procedure that hides an error encourages the application to continue with incomplete work.
This failure pattern appears in otherwise careful code. Someone adds a progress message or diagnostic SELECT and changes the behavior. The extra statement looks harmless because it does not touch the business table.
The database has a short memory for this particular value. Expecting it to remember across a paragraph of SQL is optimistic. Capture it immediately if legacy code still needs it during migration.
Use a disposable user database for the demonstrations. The tables and procedures below exist only to illustrate duplicate key failure. They are not replacements for an application's transaction policy.
Reproduce Broken @@ERROR Checks
Create a table with a primary key and one fixture row. The procedure then attempts the same key again, so expect a duplicate key error. Its success message executes before the conditional check and destroys the original error value.
CREATE TABLE dbo.ErrorCheckDemo
(
ItemID int NOT NULL CONSTRAINT PK_ErrorCheckDemo PRIMARY KEY
);
INSERT dbo.ErrorCheckDemo(ItemID) VALUES (1);
GO
CREATE PROCEDURE dbo.LegacyErrorCheckDemo
AS
BEGIN
SET NOCOUNT ON;
INSERT dbo.ErrorCheckDemo(ItemID) VALUES (1);
PRINT N'Insert attempted';
IF @@ERROR <> 0
RETURN 1;
RETURN 0;
END;
GO
DECLARE @ReturnCode int;
EXEC @ReturnCode = dbo.LegacyErrorCheckDemo;
SELECT @ReturnCode AS ProcedureReturnCode;Run this example without enabling XACT_ABORT in the session. The duplicate key statement reports an error, while the later PRINT succeeds. The return code still comes back as 0. Inspect the procedure return code beside the error message rather than trusting either alone.
The demonstration has no explicit transaction, so the failed insert leaves the original fixture untouched. More complicated legacy procedures also contain successful earlier writes. Those writes require a separate decision about rollback and ownership.
An IF is another executed statement, not a safe container for the previous status. Reading the value immediately in that condition works for that check. Reading it again afterward describes the condition's execution instead.
If you need a temporary repair, assign the value immediately after the operation. Also capture the affected row count in the same SELECT when required. This repair remains sensitive to code inserted before the capture. The insert below fails on purpose, so the capture shows error 2627 and zero rows.
DECLARE @ErrorNumber int, @AffectedRows int;
INSERT dbo.ErrorCheckDemo(ItemID) VALUES (1);
SELECT @ErrorNumber = @@ERROR, @AffectedRows = @@ROWCOUNT;
SELECT @ErrorNumber AS CapturedError, @AffectedRows AS CapturedRows;Move the Work Into TRY
A TRY block provides a boundary around related work. Execution transfers to CATCH for catchable errors, instead of relying on checks after every statement. That makes the failure path easier to review.
The replacement procedure below owns its transaction. It rejects callers that already have a transaction because its rollback would otherwise affect their work. Choose a nested transaction policy deliberately in real procedures.
Use SET XACT_ABORT ON with this transaction pattern. It helps prevent certain runtime errors from leaving partially completed transactions. It does not turn all failures into catchable errors or solve caller cancellation automatically. The final EXEC reuses key 1, so it fails on purpose and shows the CATCH path.
GO
CREATE PROCEDURE dbo.ModernErrorCheckDemo
@ItemID int
AS
BEGIN
SET NOCOUNT ON;
SET XACT_ABORT ON;
IF @@TRANCOUNT <> 0
THROW 50001, 'Run this example without an existing transaction.', 1;
BEGIN TRY
BEGIN TRANSACTION;
INSERT dbo.ErrorCheckDemo(ItemID) VALUES (@ItemID);
COMMIT TRANSACTION;
END TRY
BEGIN CATCH
SELECT ERROR_NUMBER() AS ErrorNumber,
ERROR_MESSAGE() AS ErrorMessage,
ERROR_LINE() AS ErrorLine,
XACT_STATE() AS TransactionState;
IF XACT_STATE() <> 0
ROLLBACK TRANSACTION;
THROW;
END CATCH;
END;
GO
EXEC dbo.ModernErrorCheckDemo @ItemID = 1;The diagnostic SELECT illustrates information available inside CATCH. In application code, send that information through the agreed logging path. An unexpected result set can confuse a caller that expects a different shape.
THROW without arguments re-raises the original error from CATCH. It preserves the failure instead of manufacturing a success result. The application still needs to receive and handle that error.
Read the Transaction State
XACT_STATE returns zero when no transaction exists. One means an active transaction remains committable, while negative one means it cannot commit. A doomed transaction requires rollback before further transactional writes.
In this demo the state reads -1, because XACT_ABORT dooms the transaction. The example rolls back either active state because its operation failed. That policy is appropriate for this procedure's single unit of work. Other procedures need a documented policy that agrees with their caller.
Do not use COMMIT in CATCH merely because a transaction exists. A transaction count does not tell you whether the transaction is committable. Transaction ownership and transaction state are separate parts of the decision.
Test the Caller Too
Does your application distinguish a SQL error from a normal empty result? Test that behavior with a deliberate duplicate key in a safe environment. Check the user message, retry policy, and connection cleanup.
Test a successful call with a different fixture key as well. The success path should commit exactly the intended operation. Error handling deserves both a failing test and a clean execution path.
Nested procedure calls introduce another boundary. An inner procedure that catches and suppresses an error prevents the outer procedure from seeing it. Preserve the failure unless the inner procedure completely resolves the condition.
Retries also need judgment. A duplicate key caused by invalid input will fail again without a data change. Blind retries transform one understandable failure into repeated database traffic.
Retiring @@ERROR checks also means reviewing procedure return values. A return code and a raised exception are different signals. Keep the caller's expectations consistent while changing the implementation underneath them.
Store error details after rollback when the logging design requires a database write. A write inside a doomed transaction cannot succeed. A logging failure must also preserve the original business failure for diagnosis.
Keep the Remaining Limits Visible
TRY CATCH does not catch syntax or compile errors in its own scope. Some name resolution errors at the same execution level also bypass it. Calling a lower execution level changes which scope receives the error.
Errors that terminate the connection cannot be handled by code on that closed connection. Client cancellation and timeouts also require attention in the application. Database cleanup and connection disposal belong in the caller's failure handling.
Replace @@ERROR checks around one complete transaction boundary at a time. Review all earlier successful writes and every return path during that change. A cleaner syntax still needs a correct unit of work.
After migration, keep the original error information and a deliberate rollback policy together. Confirm that callers recognize the failure and stop dependent work. Reliable handling carries the failure to the place that can resolve it.
Related reading on this blog: THROW or RAISERROR and XACT_ABORT: Why a Half-Done Transaction Can Survive an Error.
Error handling is not a sequence of hopeful checks, it is a clear failure path.
Published by Pinal Dave on SQLAuthority. More of my work at pinaldave.com.
