debugsqlMinor

How to handle errors in a transaction in a stored procedure?

Submitted by: @import:stackexchange-dba·Mar 10, 2026·

Viewed 0 times

storedhandleproceduretransactionhowerrors

Problem

I need to catch errors in a transaction in a stored procedure and log them in a table in the catch block.

After starting the transaction, a loop will attempt to insert a handful of values in the table. I surround each insert statement with a try/catch block, so that if a Primary Key violation occurs on any one of the inserts, I handle the error by inserting a record in a log table. I will then commit the transaction once the loop completes and it has attempted all inserts, even if one of them failed.

I'm concerned that calling SET XACT_ABORT ON at the beginning of the procedure will cause the transaction to be aborted when the PK error occurs, even though I'm catching and handling the error. Is that true, or does the try/catch intercept the error and suppress it in a way that the transaction is not aborted?

If try/catch doesn't stop the error from aborting the transaction, then could I call SET XACT_ABORT OFF instead to get the error trapping behavior I need?

Here is some code to test the behavior:

First, create the target table and a log table:

CREATE TABLE ErrorTestTable (a int primary key clustered)
CREATE TABLE ErrorLogTable (m nvarchar(500), d datetime2(7))

Next, create the procedure:

```
CREATE PROCEDURE [dbo].[ErrorTest]
AS
BEGIN
SET XACT_ABORT ON;
SET NOCOUNT ON;

DECLARE @Try int = 0;
DECLARE @MaxTries int = 5;

BEGIN TRAN;

WHILE @Try < @MaxTries
BEGIN

BEGIN TRY
print 'begin try - @@trancount: ' + cast(@@TranCount as nvarchar(max)) + '; xact_state: ' + cast(XACT_STATE() as nvarchar(max))
INSERT INTO ErrorTestTable (a) VALUES (1)
INSERT INTO ErrorLogTable(m,d) VALUES ('successfully inserted record!', sysutcdatetime());
END TRY
BEGIN CATCH
PRINT 'begin catch - @@trancount: ' + cast(@@TranCount as nvarchar(max)) + '; xact_state: ' + cast(XACT_STATE() as nvarchar(max))
INSERT INTO ErrorLogTable(m,d) VALUES ('pk violation!', s

Solution

Your problem stems from the fact that once the transaction becomes uncommittable (i.e. an error is raised) your loop does not honor the failure and continues to insert data into the table. When the DBE attempts to commit your subsequent changes it cannot because the transaction is no longer valid. Moving your TRY/CATCH outside of the WHILE loop resolves part of your problem.

CREATE PROCEDURE [dbo].[ErrorTest] 
AS
BEGIN
SET NOCOUNT ON;

DECLARE @error TABLE  (m nvarchar(500), d datetime2(7));
DECLARE @Try int = 0;
DECLARE @MaxTries int = 5;

BEGIN TRAN;
BEGIN TRY
    WHILE @Try  0
        ROLLBACK TRANSACTION;
END CATCH

INSERT ErrorLogTable(m,d) 
SELECT * 
  FROM @error;
END
GO

However, once the transaction is rolled back any numbers entered during the transaction will be lost.

Also, from experience I have found problems issuing an INSERT statement from inside a CATCH block. Sometimes this INSERT can also be rolled back with the transaction. However, as you can see from my answer there is a work-around which is to declare a table variable, insert your custom state into that and then perform the logging after you have correctly handled the transaction. Any data inserted into the table variable will still be available after the transaction is committed/rolled back.

You could use this to capture the numbers that are being entered, and then when the transaction encounters the primary key violation you could reinsert the numbers from the tracked table up to the number that failed, like so:

DECLARE @numbers TABLE (a INT);
DECLARE @hasError BIT = 0;
BEGIN TRAN;
BEGIN TRY
    WHILE @value  0
        ROLLBACK TRANSACTION;
    SET @hasError = 1;
END CATCH

-- re-insert the numbers that were successful 
IF @hasError = 1
BEGIN
    INSERT ErrorTestTable
    SELECT a
      FROM @numbers;
END

If you want to ensure that the subsequent numbers are entered, even if one number fails you will need to add logic outside of the transaction to do it. Personally I would rewrite the stored procedure to use a set-based operation instead of the manual loop and also include logic to try and avoid the primary key violation altogether, like so:

ALTER PROCEDURE [dbo].[ErrorTest] (
    @value INT OUTPUT,
    @max INT
)
AS
BEGIN
SET NOCOUNT ON;
DECLARE @error TABLE  (m nvarchar(500), d datetime2(7));

BEGIN TRAN;
BEGIN TRY
WITH numbers
AS
(
    SELECT ROW_NUMBER() OVER (ORDER BY o.object_id) AS RN
      FROM sys.objects AS o
CROSS JOIN sys.objects AS p
)
INSERT ErrorTestTable
SELECT RN
  FROM numbers
 WHERE rn between @value and @max
   AND NOT EXISTS (SELECT * FROM ErrorTestTable WHERE a = RN);
COMMIT TRANSACTION;
END TRY
BEGIN CATCH
INSERT INTO @error(m,d) 
VALUES ('pk violation!', sysutcdatetime());
IF @@TRANCOUNT > 0
    ROLLBACK TRANSACTION;
THROW;
END CATCH

INSERT ErrorLogTable(m,d) 
SELECT * 
  FROM @error;
END
GO

Code Snippets

CREATE PROCEDURE [dbo].[ErrorTest] 
AS
BEGIN
SET NOCOUNT ON;

DECLARE @error TABLE  (m nvarchar(500), d datetime2(7));
DECLARE @Try int = 0;
DECLARE @MaxTries int = 5;

BEGIN TRAN;
BEGIN TRY
    WHILE @Try < @MaxTries
    BEGIN

        print 'begin try - @@trancount: ' + cast(@@TranCount as nvarchar(max)) + '; xact_state: ' + cast(XACT_STATE() as nvarchar(max))
        INSERT INTO ErrorTestTable (a) 
        VALUES (1);
        INSERT INTO ErrorLogTable(m,d) 
        VALUES ('successfully inserted record!', sysutcdatetime());

        SET @Try += 1;
    END
    COMMIT TRANSACTION;
END TRY
BEGIN CATCH
    PRINT 'begin catch - @@trancount: ' + cast(@@TranCount as nvarchar(max)) + '; xact_state: ' + cast(XACT_STATE() as nvarchar(max))
    INSERT INTO @error(m,d) 
    VALUES ('pk violation!', sysutcdatetime());
    IF @@TRANCOUNT > 0
        ROLLBACK TRANSACTION;
END CATCH

INSERT ErrorLogTable(m,d) 
SELECT * 
  FROM @error;
END
GO

DECLARE @numbers TABLE (a INT);
DECLARE @hasError BIT = 0;
BEGIN TRAN;
BEGIN TRY
    WHILE @value < @max
    BEGIN
        INSERT ErrorTestTable VALUES (@value);
        INSERT @numbers VALUES (@value); -- this will not be reached if an error occurs...
        SET @value += 1;
    END
    COMMIT TRANSACTION;
END TRY
BEGIN CATCH
    IF @@TRANCOUNT > 0
        ROLLBACK TRANSACTION;
    SET @hasError = 1;
END CATCH

-- re-insert the numbers that were successful 
IF @hasError = 1
BEGIN
    INSERT ErrorTestTable
    SELECT a
      FROM @numbers;
END

ALTER PROCEDURE [dbo].[ErrorTest] (
    @value INT OUTPUT,
    @max INT
)
AS
BEGIN
SET NOCOUNT ON;
DECLARE @error TABLE  (m nvarchar(500), d datetime2(7));

BEGIN TRAN;
BEGIN TRY
WITH numbers
AS
(
    SELECT ROW_NUMBER() OVER (ORDER BY o.object_id) AS RN
      FROM sys.objects AS o
CROSS JOIN sys.objects AS p
)
INSERT ErrorTestTable
SELECT RN
  FROM numbers
 WHERE rn between @value and @max
   AND NOT EXISTS (SELECT * FROM ErrorTestTable WHERE a = RN);
COMMIT TRANSACTION;
END TRY
BEGIN CATCH
INSERT INTO @error(m,d) 
VALUES ('pk violation!', sysutcdatetime());
IF @@TRANCOUNT > 0
    ROLLBACK TRANSACTION;
THROW;
END CATCH

INSERT ErrorLogTable(m,d) 
SELECT * 
  FROM @error;
END
GO

Context

StackExchange Database Administrators Q#170654, answer score: 3

Revisions (0)

No revisions yet.