Day 20: Error handling & debugging

A. Lesson overview

Activity breakdown

Business scenario. Day 17’s Lab.usp_TransferStock returns codes that callers sometimes ignore, and nothing is recorded when a transfer fails. Operations want every failure logged and reported to the caller as a proper error, with stock never left half-moved.

B. Theory explained clearly

Part 1 — Primer: what an error is

Msg 547, Level 16, State 0, Line 3
The UPDATE statement conflicted with the CHECK constraint "CK_LabStock_NonNegative". …

What does an error stop? This is the part that surprises people. With the default SET XACT_ABORT OFF:

So, with the defaults, this commits half a transfer (Day 16 showed the effect):

BEGIN TRANSACTION;
    UPDATE … add 20 to warehouse 2 …;        -- succeeds
    UPDATE … remove 20 from warehouse 1 …;   -- fails: CHECK constraint
COMMIT TRANSACTION;                          -- runs! commits the first update

Part 2 — TRY…CATCH

BEGIN TRY
    -- statements
END TRY
BEGIN CATCH
    SELECT ERROR_NUMBER()    AS ErrorNumber,
           ERROR_SEVERITY()  AS Severity,
           ERROR_STATE()     AS State,
           ERROR_PROCEDURE() AS ProcedureName,   -- NULL outside a procedure or trigger
           ERROR_LINE()      AS LineNumber,
           ERROR_MESSAGE()   AS Message;
END CATCH;

When an error with severity 11 or higher that doesn’t close the connection happens inside TRY, control jumps to CATCH. The ERROR_*() functions return information about that error anywhere inside the CATCH block — including inside procedures called from it — and NULL elsewhere.

CATCH does not handle:

• warnings and messages with severity 10 or lower;
• severity 20+ errors that close the connection;
• client attention events — query timeouts and cancels from the application (the batch just stops, possibly leaving a transaction open);
• compile errors and object-name resolution errors in the same scope as the TRY (e.g. a table that doesn’t exist). The same error is caught if it happens one level down, inside a procedure or sp_executesql called from the TRY.

The attention case is a key reason for SET XACT_ABORT ON (Part 4): it ensures the open transaction is rolled back when a timeout stops the batch.

Part 3 — THROW and RAISERROR

THROW 50101, N'Quantity must be positive.', 1;     -- custom error: number ≥ 50000, message, state
THROW;                                             -- inside CATCH: re-raise the caught error unchanged

Use THROW in new code. You will still meet RAISERROR in older code — and RAISERROR(…, 10, 1) WITH NOWAIT is still handy for progress messages.

Part 4 — Transactions in CATCH

When you land in CATCH, a transaction may be open and may or may not be usable. XACT_STATE() tells you:

@@TRANCOUNT only tells you whether a transaction is open, not whether it can be committed.

SET XACT_ABORT ON makes any run-time error roll back the whole transaction and abort the batch — or, inside TRY, jump to CATCH with the transaction doomed (XACT_STATE() = -1). That gives the simple, safe rule for data-changing code: any error → roll back everything. XACT_ABORT ON is the widely recommended setting for procedures with explicit transactions.

Part 5 — The standard pattern

CREATE OR ALTER PROCEDURE Lab.usp_Something
    @Param int
AS
BEGIN
    SET NOCOUNT ON;
    SET XACT_ABORT ON;

    BEGIN TRY
        -- validate input: THROW 501xx for business-rule failures
        BEGIN TRANSACTION;
            -- changes
        COMMIT TRANSACTION;
    END TRY
    BEGIN CATCH
        IF XACT_STATE() <> 0 ROLLBACK TRANSACTION;   -- 1. undo
        EXEC Lab.usp_LogError;                        -- 2. record (after the rollback!)
        THROW;                                        -- 3. tell the caller
    END CATCH;
END;

Why log after the rollback? A log row written inside the transaction is undone by the rollback. Write it after — or, if you must capture values before rolling back, hold them in variables or a table variable (which ignore rollbacks, Day 15).

Return codes or errors? Day 17 used return codes for expected refusals. Errors are harder to ignore: an unhandled error stops the caller’s batch and shows up in applications as an exception. Many teams use errors for all failures and keep return codes for success variations. Whichever you choose, be consistent across a database.

C. Dataset and starting state

Setup (re-runnable; resets the Lab copies and the error log). This is the Day 17 setup plus the error log table. It replaces Lab.Customer, removing any Day 19 triggers on it.

USE RetailLab;
GO

DROP TABLE IF EXISTS Lab.Customer, Lab.Stock, Lab.ErrorLog;

SELECT * INTO Lab.Customer FROM Sales.Customer;
ALTER TABLE Lab.Customer ADD CONSTRAINT PK_LabCustomer PRIMARY KEY (CustomerID);
CREATE UNIQUE INDEX UX_LabCustomer_Email ON Lab.Customer (Email) WHERE Email IS NOT NULL;

SELECT WarehouseID, ProductID, QuantityOnHand, ReorderLevel INTO Lab.Stock FROM Inventory.Stock;
ALTER TABLE Lab.Stock ADD CONSTRAINT PK_LabStock PRIMARY KEY (WarehouseID, ProductID);
ALTER TABLE Lab.Stock ADD CONSTRAINT CK_LabStock_NonNegative CHECK (QuantityOnHand >= 0);

CREATE TABLE Lab.ErrorLog
(
    ErrorLogID    int IDENTITY(1,1) NOT NULL CONSTRAINT PK_ErrorLog PRIMARY KEY,
    LoggedAt      datetime2(0)   NOT NULL CONSTRAINT DF_ErrorLog_LoggedAt DEFAULT (SYSDATETIME()),
    LoginName     sysname        NOT NULL CONSTRAINT DF_ErrorLog_Login    DEFAULT (ORIGINAL_LOGIN()),
    ErrorNumber   int            NOT NULL,
    Severity      int            NULL,
    ErrorState    int            NULL,
    ProcedureName nvarchar(128)  NULL,
    LineNumber    int            NULL,
    ErrorMessage  nvarchar(4000) NOT NULL
);
GO

Validation. Expected: 40, 1142, 0, 0.

SELECT  (SELECT COUNT(*) FROM Lab.Customer)         AS LabCustomers,
        (SELECT SUM(QuantityOnHand) FROM Lab.Stock) AS TotalUnits,
        (SELECT COUNT(*) FROM Lab.ErrorLog)         AS LoggedErrors,
        @@TRANCOUNT                                 AS OpenTran;

Stock rows used today: product 1 — warehouse 1: 16, warehouse 2: 53; product 2 — warehouse 1: 25, warehouse 2: 0; product 3 — warehouse 1 only: 40; product 26 — warehouse 1: 18, warehouse 2: 40.

D. Worked examples

Worked example 1 — The half-committed transfer, again

SET XACT_ABORT OFF;      -- the default, stated explicitly for the demonstration

BEGIN TRANSACTION;
    UPDATE Lab.Stock SET QuantityOnHand = QuantityOnHand + 20 WHERE WarehouseID = 2 AND ProductID = 1;
    UPDATE Lab.Stock SET QuantityOnHand = QuantityOnHand - 20 WHERE WarehouseID = 1 AND ProductID = 1;  -- 16 - 20 < 0
COMMIT TRANSACTION;

SELECT SUM(QuantityOnHand) AS TotalUnits, @@TRANCOUNT AS OpenTran FROM Lab.Stock;

Expected result: (1 row affected), then Msg 547 (CHECK constraint), then the COMMIT runs: TotalUnits 1162, OpenTran 0. Twenty units were created from nothing.

Undo it before continuing:

UPDATE Lab.Stock SET QuantityOnHand = QuantityOnHand - 20 WHERE WarehouseID = 2 AND ProductID = 1;
SELECT SUM(QuantityOnHand) AS TotalUnits FROM Lab.Stock;      -- 1142

Worked example 2 — The same transfer, protected

SET XACT_ABORT ON;

BEGIN TRY
    BEGIN TRANSACTION;
        UPDATE Lab.Stock SET QuantityOnHand = QuantityOnHand + 20 WHERE WarehouseID = 2 AND ProductID = 1;
        UPDATE Lab.Stock SET QuantityOnHand = QuantityOnHand - 20 WHERE WarehouseID = 1 AND ProductID = 1;
    COMMIT TRANSACTION;
END TRY
BEGIN CATCH
    SELECT ERROR_NUMBER() AS ErrorNumber, ERROR_LINE() AS LineNumber,
           XACT_STATE()   AS XactState,   @@TRANCOUNT  AS TranCount;
    IF XACT_STATE() <> 0 ROLLBACK TRANSACTION;
END CATCH;

SELECT SUM(QuantityOnHand) AS TotalUnits, @@TRANCOUNT AS OpenTran FROM Lab.Stock;
SET XACT_ABORT OFF;

Expected result: ErrorNumber 547, LineNumber 6 if you executed the block on its own (line numbers count from the first line of the batch you ran), XactState −1, TranCount 1. Then TotalUnits 1142, OpenTran 0. No error message reaches the client: the CATCH handled it. Real code should log and THROW — see the lab.

Worked example 3 — THROW, re-throw, and what CATCH misses

-- (a) Custom error with a formatted message
DECLARE @Available int = 3, @Msg nvarchar(2048);
SET @Msg = FORMATMESSAGE(N'Only %d unit(s) available in warehouse %d.', @Available, 2);

BEGIN TRY
    THROW 50103, @Msg, 1;
END TRY
BEGIN CATCH
    SELECT ERROR_NUMBER() AS ErrorNumber, ERROR_SEVERITY() AS Severity, ERROR_MESSAGE() AS Message;
END CATCH;
GO

-- (b) Catch, then re-throw unchanged
BEGIN TRY
    SELECT 1 / 0;
END TRY
BEGIN CATCH
    PRINT N'Logging would happen here.';
    THROW;
END CATCH;
GO

-- (c) Not caught: missing table in the same scope
BEGIN TRY
    SELECT * FROM Lab.NoSuchTable;
END TRY
BEGIN CATCH
    SELECT N'Caught' AS Result;
END CATCH;
GO

-- (d) Caught: the same error one level down
BEGIN TRY
    EXEC sys.sp_executesql N'SELECT * FROM Lab.NoSuchTable;';
END TRY
BEGIN CATCH
    SELECT N'Caught' AS Result, ERROR_NUMBER() AS ErrorNumber;
END CATCH;
GO

Expected result:

• (a) 50103, 16, Only 3 unit(s) available in warehouse 2.
• (b) the message Logging would happen here., then Msg 8134, Level 16 … Divide by zero error encountered. — the original error, re-raised.
• (c) Msg 208 … Invalid object name ‘Lab.NoSuchTable’. — the CATCH block never ran.
• (d) Caught, 208.

Worked example 4 — A reusable error logger

CREATE OR ALTER PROCEDURE Lab.usp_LogError
AS
BEGIN
    SET NOCOUNT ON;

    IF ERROR_NUMBER() IS NULL RETURN;          -- not called from a CATCH block
    IF XACT_STATE() = -1                       -- cannot write inside a doomed transaction
    BEGIN
        PRINT N'usp_LogError: roll back before logging.';
        RETURN;
    END;

    INSERT INTO Lab.ErrorLog (ErrorNumber, Severity, ErrorState, ProcedureName, LineNumber, ErrorMessage)
    VALUES (ERROR_NUMBER(), ERROR_SEVERITY(), ERROR_STATE(), ERROR_PROCEDURE(), ERROR_LINE(), ERROR_MESSAGE());
END;
GO

BEGIN TRY
    SELECT CAST(N'abc' AS int);
END TRY
BEGIN CATCH
    EXEC Lab.usp_LogError;
END CATCH;

SELECT ErrorNumber, Severity, ErrorMessage FROM Lab.ErrorLog ORDER BY ErrorLogID;

Expected result: one log row: 245, 16, Conversion failed when converting the nvarchar value ‘abc’ to data type int. The logger works because the ERROR_*() functions keep their values inside procedures called from CATCH. Clear it before the lab: TRUNCATE TABLE Lab.ErrorLog;.

E. Guided practical lab — Rebuild usp_TransferStock with proper error handling

Replace return codes for failures with errors (keeping @Message and RETURN 0 for success), log every failure, and prove the stock is never left half-moved.

Step 1 — The new version

1. Do: Rewrite the procedure to the standard pattern.
2. Run:

   USE RetailLab;
   GO
   TRUNCATE TABLE Lab.ErrorLog;
   GO
   
   CREATE OR ALTER PROCEDURE Lab.usp_TransferStock
       @ProductID     int,
       @FromWarehouse int,
       @ToWarehouse   int,
       @Qty           int,
       @Message       nvarchar(200) OUTPUT
   AS
   BEGIN
       SET NOCOUNT ON;
       SET XACT_ABORT ON;
   
       DECLARE @Available int, @Rows int, @Err nvarchar(2048);
   
       BEGIN TRY
           IF @Qty IS NULL OR @Qty <= 0 OR @FromWarehouse = @ToWarehouse
               THROW 50101, N'Invalid input: quantity must be positive and the warehouses must differ.', 1;
   
           BEGIN TRANSACTION;
   
           SELECT @Available = MAX(CASE WHEN WarehouseID = @FromWarehouse THEN QuantityOnHand END),
                  @Rows      = COUNT(*)
           FROM   Lab.Stock WITH (UPDLOCK, HOLDLOCK)
           WHERE  ProductID = @ProductID
             AND  WarehouseID IN (@FromWarehouse, @ToWarehouse);
   
           IF @Rows < 2
           BEGIN
               SET @Err = FORMATMESSAGE(N'Product %d has no stock record in warehouse %d or %d.',
                                        @ProductID, @FromWarehouse, @ToWarehouse);
               THROW 50102, @Err, 1;
           END;
   
           IF @Available < @Qty
           BEGIN
               SET @Err = FORMATMESSAGE(N'Only %d unit(s) of product %d available in warehouse %d.',
                                        @Available, @ProductID, @FromWarehouse);
               THROW 50103, @Err, 1;
           END;
   
           UPDATE Lab.Stock
           SET    QuantityOnHand = QuantityOnHand + CASE WHEN WarehouseID = @FromWarehouse THEN -@Qty ELSE @Qty END
           WHERE  ProductID = @ProductID
             AND  WarehouseID IN (@FromWarehouse, @ToWarehouse);
   
           COMMIT TRANSACTION;
   
           SET @Message = CONCAT(N'Transferred ', @Qty, N' unit(s) of product ', @ProductID, N'.');
           RETURN 0;
       END TRY
       BEGIN CATCH
           IF XACT_STATE() <> 0 ROLLBACK TRANSACTION;
           EXEC Lab.usp_LogError;
           THROW;
       END CATCH;
   END;
   GO

3. Observe: Commands completed successfully.
4. Why it matters: Every failure path now goes through one CATCH: roll back if needed, log, re-raise. The THROW inside TRY jumps to CATCH like any other error, so business-rule failures are logged too.
5. Confirm: SELECT COUNT(*) FROM Lab.ErrorLog; → 0.

Step 2 — A test harness that catches errors

1. Do: Call the procedure for the five Day 17 test cases, catching each error.

2. Run:

   DECLARE @Results TABLE (TestCase nvarchar(40), ErrorNumber int, Msg nvarchar(2048));
   DECLARE @msg nvarchar(200);
   
   BEGIN TRY EXEC Lab.usp_TransferStock 26, 2, 1, 10, @msg OUTPUT; INSERT @Results VALUES (N'Valid transfer', 0, @msg); END TRY
   BEGIN CATCH INSERT @Results VALUES (N'Valid transfer', ERROR_NUMBER(), ERROR_MESSAGE()); END CATCH;
   
   BEGIN TRY EXEC Lab.usp_TransferStock 26, 2, 2, 10, @msg OUTPUT; INSERT @Results VALUES (N'Same warehouse', 0, @msg); END TRY
   BEGIN CATCH INSERT @Results VALUES (N'Same warehouse', ERROR_NUMBER(), ERROR_MESSAGE()); END CATCH;
   
   BEGIN TRY EXEC Lab.usp_TransferStock 26, 2, 1, 0, @msg OUTPUT; INSERT @Results VALUES (N'Zero quantity', 0, @msg); END TRY
   BEGIN CATCH INSERT @Results VALUES (N'Zero quantity', ERROR_NUMBER(), ERROR_MESSAGE()); END CATCH;
   
   BEGIN TRY EXEC Lab.usp_TransferStock 3, 1, 2, 1, @msg OUTPUT; INSERT @Results VALUES (N'No destination record', 0, @msg); END TRY
   BEGIN CATCH INSERT @Results VALUES (N'No destination record', ERROR_NUMBER(), ERROR_MESSAGE()); END CATCH;
   
   BEGIN TRY EXEC Lab.usp_TransferStock 2, 2, 1, 5, @msg OUTPUT; INSERT @Results VALUES (N'Not enough stock', 0, @msg); END TRY
   BEGIN CATCH INSERT @Results VALUES (N'Not enough stock', ERROR_NUMBER(), ERROR_MESSAGE()); END CATCH;
   
   SELECT * FROM @Results;

3. Observe:

   TestCase	ErrorNumber	Msg
   Valid transfer	0	Transferred 10 unit(s) of product 26.
   Same warehouse	50101	Invalid input: quantity must be positive and the warehouses must differ.
   Zero quantity	50101	Invalid input: quantity must be positive and the warehouses must differ.
   No destination record	50102	Product 3 has no stock record in warehouse 1 or 2.
   Not enough stock	50103	Only 0 unit(s) of product 2 available in warehouse 2.

4. Why it matters: The caller now has to deal with failures — an uncaught error would stop its batch. Error numbers give callers something stable to test (IF ERROR_NUMBER() = 50103 …).

5. Confirm: 5 rows as above.

Step 3 — Check the invariant and the log

1. Do: Confirm stock is consistent and every failure was logged.
2. Run:

   SELECT SUM(QuantityOnHand) AS TotalUnits, @@TRANCOUNT AS OpenTran FROM Lab.Stock;
   SELECT WarehouseID, QuantityOnHand FROM Lab.Stock WHERE ProductID = 26 ORDER BY WarehouseID;
   SELECT ErrorNumber, LineNumber, ErrorMessage FROM Lab.ErrorLog ORDER BY ErrorLogID;

3. Observe: 1142, 0; product 26: warehouse 1 = 28, warehouse 2 = 30; 4 log rows with numbers 50101, 50101, 50102, 50103 and the same messages as the harness.
4. Why it matters: The two failures that happened inside the transaction (50102, 50103) were rolled back before logging, so their log rows survived.
5. Confirm: 4 log rows.

Step 4 — Break the order on purpose

1. Do: See what happens if you log before rolling back. Make a throw-away copy of the pattern:
2. Run:

   SET XACT_ABORT ON;
   BEGIN TRY
       BEGIN TRANSACTION;
           UPDATE Lab.Stock SET QuantityOnHand = -1 WHERE WarehouseID = 1 AND ProductID = 1;
       COMMIT TRANSACTION;
   END TRY
   BEGIN CATCH
       EXEC Lab.usp_LogError;                    -- wrong order
       IF XACT_STATE() <> 0 ROLLBACK TRANSACTION;
   END CATCH;
   SET XACT_ABORT OFF;
   
   SELECT COUNT(*) AS LoggedErrors FROM Lab.ErrorLog;

3. Observe: the message usp_LogError: roll back before logging.; LoggedErrors is still 4.
4. Why it matters: In a doomed transaction no writes are allowed, so the logger refuses. Even with XACT_ABORT OFF (state 1), a log row written before the rollback would be rolled back with everything else. Roll back first, then log.
5. Confirm: 4 log rows; @@TRANCOUNT 0.

F. Independent practice

Task 1 — XACT_STATE() with and without XACT_ABORT

Inside TRY, open a transaction and run an update that breaks the stock CHECK constraint (remove 100 from warehouse 1, product 1). In CATCH, select ERROR_NUMBER(), XACT_STATE() and @@TRANCOUNT, then roll back. Run it twice: with SET XACT_ABORT OFF and with ON.

Success criteria: OFF → 547, 1, 1; ON → 547, -1, 1. Total units remain 1142. In one sentence: why is state 1 dangerous in code that ends with COMMIT?

Task 2 — Rebuild usp_AddCustomer

Rewrite Lab.usp_AddCustomer (same parameters) to the standard pattern: missing first or last name → error 50200; existing email → error 50201 with the message A customer with email {email} already exists. (built with FORMATMESSAGE, email trimmed and lower-cased). If a duplicate slips past the check and the unique index raises 2601, translate it to 50201 too. Log every failure. Test: add Daisy Holt with ' Daisy.Holt@Example.com' (Bath, United Kingdom); add her again with 'daisy.holt@example.com'; add a customer with a blank first name.

Success criteria: first call → @NewCustomerID 41, email stored as daisy.holt@example.com; second → 50201 A customer with email daisy.holt@example.com already exists.; third → 50200. Lab.ErrorLog gains 2 rows. Lab.Customer has 41 rows.

Task 3 — All-or-nothing batch of transfers

In one outer transaction, call Lab.usp_TransferStock twice: first product 1, warehouse 2 → 1, quantity 5 (valid); then product 2, warehouse 2 → 1, quantity 5 (fails). Catch the error in the caller and roll back if needed. Check product 1’s stock and @@TRANCOUNT afterwards.

Success criteria: the caller’s CATCH runs (error 50103 — see the solution about message 266); product 1 is unchanged (warehouse 1 = 16, warehouse 2 = 53); @@TRANCOUNT 0; total units 1142. Explain why the first, valid transfer did not survive.

Stretch challenge — Retry on deadlock

Write Lab.usp_TransferStockWithRetry with the same parameters plus @MaxAttempts int = 3. It calls Lab.usp_TransferStock, and if the error is a deadlock (1205) and attempts remain, waits 200 ms and tries again; any other error is re-thrown immediately. Test the success path and a non-deadlock error.

Success criteria: a valid transfer (product 26, warehouse 1 → 2, quantity 1) succeeds; an invalid one (product 2, warehouse 2 → 1, quantity 5) raises 50103 once, adding exactly one row to Lab.ErrorLog (it was not retried). Producing a real deadlock needs two sessions; Day 16’s Session A/B lab shows how, and is optional here.

G. Solutions and explanations

SET XACT_ABORT OFF;      -- run once with OFF, then change to ON and run again
BEGIN TRY
    BEGIN TRANSACTION;
        UPDATE Lab.Stock SET QuantityOnHand = QuantityOnHand - 100 WHERE WarehouseID = 1 AND ProductID = 1;
    COMMIT TRANSACTION;
END TRY
BEGIN CATCH
    SELECT ERROR_NUMBER() AS ErrorNumber, XACT_STATE() AS XactState, @@TRANCOUNT AS TranCount;
    IF XACT_STATE() <> 0 ROLLBACK TRANSACTION;
END CATCH;
SET XACT_ABORT OFF;

SELECT SUM(QuantityOnHand) AS TotalUnits FROM Lab.Stock;

CREATE OR ALTER PROCEDURE Lab.usp_AddCustomer
    @FirstName     nvarchar(50),
    @LastName      nvarchar(50),
    @Email         nvarchar(255) = NULL,
    @City          nvarchar(60),
    @Country       nvarchar(60),
    @NewCustomerID int OUTPUT
AS
BEGIN
    SET NOCOUNT ON;
    SET XACT_ABORT ON;

    DECLARE @Err nvarchar(2048);
    SET @Email = LOWER(TRIM(@Email));

    BEGIN TRY
        IF NULLIF(TRIM(@FirstName), N'') IS NULL OR NULLIF(TRIM(@LastName), N'') IS NULL
            THROW 50200, N'First name and last name are required.', 1;

        IF @Email IS NOT NULL AND EXISTS (SELECT 1 FROM Lab.Customer WHERE Email = @Email)
        BEGIN
            SET @Err = FORMATMESSAGE(N'A customer with email %s already exists.', @Email);
            THROW 50201, @Err, 1;
        END;

        BEGIN TRANSACTION;

        SELECT @NewCustomerID = COALESCE(MAX(CustomerID), 0) + 1
        FROM   Lab.Customer WITH (UPDLOCK, HOLDLOCK);

        INSERT INTO Lab.Customer (CustomerID, FirstName, LastName, Email, Phone, City, Country,
                                  CustomerSegment, DateJoined, IsActive)
        VALUES (@NewCustomerID, TRIM(@FirstName), TRIM(@LastName), @Email, NULL,
                TRIM(@City), TRIM(@Country), 'Consumer', '2026-01-02', 1);

        COMMIT TRANSACTION;
        RETURN 0;
    END TRY
    BEGIN CATCH
        IF XACT_STATE() <> 0 ROLLBACK TRANSACTION;
        EXEC Lab.usp_LogError;

        IF ERROR_NUMBER() IN (2601, 2627)            -- unique index / constraint: same meaning as 50201
        BEGIN
            SET @Err = FORMATMESSAGE(N'A customer with email %s already exists.', @Email);
            THROW 50201, @Err, 1;
        END;
        THROW;
    END CATCH;
END;
GO

DECLARE @id int;
EXEC Lab.usp_AddCustomer @FirstName = N'Daisy', @LastName = N'Holt', @Email = N' Daisy.Holt@Example.com',
     @City = N'Bath', @Country = N'United Kingdom', @NewCustomerID = @id OUTPUT;
SELECT @id AS NewCustomerID, Email FROM Lab.Customer WHERE CustomerID = @id;
GO
DECLARE @id int;
BEGIN TRY
    EXEC Lab.usp_AddCustomer @FirstName = N'Daisy', @LastName = N'Holt', @Email = N'daisy.holt@example.com',
         @City = N'Bath', @Country = N'United Kingdom', @NewCustomerID = @id OUTPUT;
END TRY
BEGIN CATCH
    SELECT ERROR_NUMBER() AS ErrorNumber, ERROR_MESSAGE() AS Msg;
END CATCH;

BEGIN TRY
    EXEC Lab.usp_AddCustomer @FirstName = N'  ', @LastName = N'Nobody', @City = N'Leeds',
         @Country = N'United Kingdom', @NewCustomerID = @id OUTPUT;
END TRY
BEGIN CATCH
    SELECT ERROR_NUMBER() AS ErrorNumber, ERROR_MESSAGE() AS Msg;
END CATCH;

SELECT COUNT(*) AS LabCustomers FROM Lab.Customer;
SELECT ErrorNumber, ErrorMessage FROM Lab.ErrorLog WHERE ErrorNumber IN (50200, 50201) ORDER BY ErrorLogID;

SET XACT_ABORT ON;
DECLARE @msg nvarchar(200);

BEGIN TRY
    BEGIN TRANSACTION;
        EXEC Lab.usp_TransferStock 1, 2, 1, 5, @msg OUTPUT;     -- valid
        EXEC Lab.usp_TransferStock 2, 2, 1, 5, @msg OUTPUT;     -- fails: 50103
    COMMIT TRANSACTION;
END TRY
BEGIN CATCH
    SELECT ERROR_NUMBER() AS ErrorNumber, ERROR_MESSAGE() AS Msg, XACT_STATE() AS XactState;
    IF XACT_STATE() <> 0 ROLLBACK TRANSACTION;
END CATCH;
SET XACT_ABORT OFF;

SELECT WarehouseID, QuantityOnHand FROM Lab.Stock WHERE ProductID = 1 ORDER BY WarehouseID;
SELECT SUM(QuantityOnHand) AS TotalUnits, @@TRANCOUNT AS OpenTran FROM Lab.Stock;

CREATE OR ALTER PROCEDURE Lab.usp_TransferStockWithRetry
    @ProductID     int,
    @FromWarehouse int,
    @ToWarehouse   int,
    @Qty           int,
    @MaxAttempts   int = 3
AS
BEGIN
    SET NOCOUNT ON;
    DECLARE @Attempt int = 0, @msg nvarchar(200);

    WHILE 1 = 1
    BEGIN
        SET @Attempt += 1;
        BEGIN TRY
            EXEC Lab.usp_TransferStock @ProductID, @FromWarehouse, @ToWarehouse, @Qty, @msg OUTPUT;
            PRINT CONCAT(@msg, N' Attempts: ', @Attempt, N'.');
            RETURN 0;
        END TRY
        BEGIN CATCH
            IF XACT_STATE() <> 0 ROLLBACK TRANSACTION;
            IF ERROR_NUMBER() = 1205 AND @Attempt < @MaxAttempts
            BEGIN
                WAITFOR DELAY '00:00:00.200';
                CONTINUE;                              -- deadlock victim: try again
            END;
            THROW;                                     -- anything else, or out of attempts
        END CATCH;
    END;
END;
GO

DECLARE @before int = (SELECT COUNT(*) FROM Lab.ErrorLog);
EXEC Lab.usp_TransferStockWithRetry 26, 1, 2, 1;                -- prints "... Attempts: 1."
BEGIN TRY
    EXEC Lab.usp_TransferStockWithRetry 2, 2, 1, 5;
END TRY
BEGIN CATCH
    SELECT ERROR_NUMBER() AS ErrorNumber,
           (SELECT COUNT(*) FROM Lab.ErrorLog) - @before AS NewLogRows;
END CATCH;

H. Knowledge check

1. Understanding. With default settings, a transaction contains three UPDATEs; the second fails with a foreign-key violation. If the script ends with COMMIT, what is committed?

2. Query interpretation. In a CATCH block, @@TRANCOUNT is 1 and XACT_STATE() is −1. What does that mean, and what is the only thing you can do with the transaction?

3. Debugging. A procedure logs errors to a table in its CATCH block, but the log is always empty although errors are reported. What is the likely cause?

4. Understanding. Why should a data-changing procedure set XACT_ABORT ON even though it uses TRY…CATCH?

5. Debugging. BEGIN TRY SELECT * FROM Sales.Custmer; END TRY BEGIN CATCH PRINT 'caught'; END CATCH; shows Msg 208 and never prints caught. Why?

I. Troubleshooting and cleanup

Cleanup. None required. Keep Lab.ErrorLog, Lab.usp_LogError and the new versions of the two procedures — Day 21 uses them. To start today again, re-run the setup in section C (and re-run the lab’s CREATE OR ALTER statements). Core data is unchanged.

J. Completion checklist, recap and references

Completion checklist

• Worked example 1 reached 1162 units and you restored 1142.
• Worked example 2 showed XACT_STATE() −1 and left 1142 units.
• You saw that CATCH misses a missing table in the same scope but catches it through sp_executesql.
• The harness produced the five rows; the log has 4 rows; product 26 is 28 / 30.
• Tasks 1–3 match the success criteria.

Recap

• With default settings, many errors end only the statement — the transaction stays open and can commit half the work.
• TRY…CATCH with the ERROR_*() functions handles errors of severity 11 or higher that don’t close the connection — not timeouts, fatal errors, or name errors in the same scope.
• THROW raises custom errors (≥ 50000) and re-throws caught ones unchanged; the previous statement needs a semicolon.
• XACT_STATE(): 1 committable, 0 none, −1 doomed. SET XACT_ABORT ON makes every error doom the transaction.
• Standard pattern: XACT_ABORT ON → TRY → transaction → CATCH: roll back → log → THROW.

Next: Day 21 — Week 3 review: a small, audited CRUD system for products built with procedures, a trigger, transactions and the error handling from today.

References (Microsoft Learn)

• TRY…CATCH (Transact-SQL)
• THROW (Transact-SQL)
• RAISERROR (Transact-SQL)
• XACT_STATE (Transact-SQL)
• SET XACT_ABORT (Transact-SQL)
• FORMATMESSAGE (Transact-SQL)
• Database engine error severities
• ERROR_MESSAGE (Transact-SQL)