TRY/CATCH and XACT_STATE for safe error handling

This lesson uses DDI_SQL_Training dataset v1.0.0 on SQL Server 2019 or later. Inspect transaction state after a deliberate duplicate-key error on a temp table. Run changing examples only on a disposable training instance. The example uses a session-local object or rolls back its changes.

Run the example

CREATE TABLE #Keys (ID int NOT NULL PRIMARY KEY);
SET XACT_ABORT ON;
BEGIN TRY
    BEGIN TRANSACTION;
    INSERT INTO #Keys VALUES (1);
    INSERT INTO #Keys VALUES (1);
    COMMIT TRANSACTION;
END TRY
BEGIN CATCH
    DECLARE @StateInCatch int = XACT_STATE();
    IF XACT_STATE() <> 0 ROLLBACK TRANSACTION;
    SELECT @StateInCatch AS StateInCatch, XACT_STATE() AS StateAfterRollback;
END CATCH;
DROP TABLE #Keys;
SET XACT_ABORT OFF;

Read it step by step

1. The second INSERT violates the temp table primary key and transfers control to CATCH.
2. XACT_STATE reports whether the transaction is active and committable; with XACT_ABORT ON this error makes it uncommittable.
3. The CATCH rolls back before reporting state 0 afterward.

Check the result

The deliberate error is handled; no canonical row is touched.

Practice

Repeat with an attempted duplicate ID 2 and report the same two transaction states.

CREATE TABLE #Keys (ID int NOT NULL PRIMARY KEY);
SET XACT_ABORT ON;
BEGIN TRY
    BEGIN TRANSACTION;
    INSERT INTO #Keys VALUES (2);
    INSERT INTO #Keys VALUES (2);
    COMMIT TRANSACTION;
END TRY
BEGIN CATCH
    DECLARE @StateInCatch int = XACT_STATE();
    IF XACT_STATE() <> 0 ROLLBACK TRANSACTION;
    SELECT @StateInCatch AS StateInCatch, XACT_STATE() AS StateAfterRollback;
END CATCH;
DROP TABLE #Keys;
SET XACT_ABORT OFF;