T-SQL Error Handling

Status: Reviewed (deep-dive v1)
Stack note: Write procs (usp_Alert_Ack, merges, Evaluate) and Agent jobs need predictable failures — catch, log, don’t leave half-open transactions. Blazor shows friendly errors from proc/RAISERROR messages; don’t swallow silently.
Pairs with: SQL-12 (proc skeleton), SQL-10 / SPIKE-07 (alerts), SQL-04 (Agent jobs).
Goal: TRY/CATCH, XACT_STATE, XACT_ABORT, logging patterns for warehouse + jobs.

Quick chooser

Situation	Pattern
Read-only board proc	Light touch — let errors bubble; optional outer TRY for logging
Write proc (Ack, merge)	`SET XACT_ABORT ON` + `BEGIN TRY/TRAN` + CATCH with `XACT_STATE`
Agent job step	TRY/CATCH + log table or `RAISERROR` so job history shows failure
“Continue on row errors” bulk	Prefer set-based + staging validation; don’t hide poison rows
Blazor needs a message	`THROW;` or `RAISERROR` with clear text after rollback

Core template (write proc)

CREATE OR ALTER PROC dbo.usp_Example_Write
  @Id int,
  @Actor nvarchar(128)
AS
BEGIN
  SET NOCOUNT ON;
  SET XACT_ABORT ON;

  BEGIN TRY
    BEGIN TRAN;

    -- validate
    IF NOT EXISTS (SELECT 1 FROM dbo.Alert WHERE AlertId = @Id AND Status = N'Open')
      THROW 50001, N'Alert not open or not found.', 1;

    UPDATE dbo.Alert
      SET Status = N'Acked', AckedAt = SYSUTCDATETIME(), AckedBy = @Actor
    WHERE AlertId = @Id;

    INSERT dbo.AlertEvent (AlertId, EventType, Actor, Message)
    VALUES (@Id, N'Acked', @Actor, NULL);

    COMMIT TRAN;
  END TRY
  BEGIN CATCH
    IF XACT_STATE() <> 0
      ROLLBACK TRAN;

    -- optional: INSERT dbo.ProcErrorLog (…)

    THROW;  -- re-raise original error to Blazor/Agent
  END CATCH
END;

`XACT_STATE` cheat sheet

Value	Meaning	Action in CATCH
`1`	Transaction usable	`ROLLBACK` (or commit only if you truly know it’s safe — usually rollback)
`0`	No transaction	Nothing to roll back
`-1`	Uncommittable (doomed)	Must `ROLLBACK`; cannot commit

With XACT_ABORT ON, many errors doom the tran → expect -1 and always roll back.

`TRY/CATCH` vs `XACT_ABORT`

XACT_ABORT ON: Most runtime errors abort the batch and doom the tran — pairs well with TRY/CATCH for a single cleanup path
XACT_ABORT OFF (default): Some errors are catchable without dooming — easier to leave partial work if you’re sloppy
Team default for write procs: XACT_ABORT ON + TRY/CATCH + THROW

Note: Not all errors enter CATCH (e.g. some compile-time / certain connection-level issues). Jobs should still check step success.

THROW vs RAISERROR

	`THROW`	`RAISERROR`
Re-raise in CATCH	`THROW;` (preserves error)	Manual rebuild — easy to lose number/line
Custom message	`THROW 50001, N'msg', 1;`	Flexible severity/state
Preference	Default in new code	Legacy / special severity needs

Custom numbers: use ≥ 50000. Document codes your Blazor layer maps (e.g. 50001 = not found / conflict).

Logging patterns (estate)

A. Application / Blazor

Catch SqlException; show message; log server-side with proc name + user
Don’t return stack traces to browser

B. Table log (ops)

-- lean shape
CREATE TABLE dbo.ProcErrorLog (
  LogId bigint IDENTITY PRIMARY KEY,
  LoggedAt datetime2(0) NOT NULL CONSTRAINT DF_ProcErrorLog_At DEFAULT SYSUTCDATETIME(),
  ProcName sysname NULL,
  ErrorNumber int NULL,
  ErrorSeverity int NULL,
  ErrorState int NULL,
  ErrorLine int NULL,
  ErrorMessage nvarchar(4000) NULL,
  Actor nvarchar(128) NULL
);
-- In CATCH before THROW:
-- INSERT dbo.ProcErrorLog (ProcName, ErrorNumber, …)
-- SELECT ERROR_PROCEDURE(), ERROR_NUMBER(), ERROR_SEVERITY(), ERROR_STATE(), ERROR_LINE(), ERROR_MESSAGE(), @Actor;

C. SQL Agent

Step fails if unhandled error / THROW after rollback
Enable email/Operator on failure (SQL-10)
Prefer failing loud over RETURN 0 after swallow

D. Alert evaluate

usp_Alert_Evaluate should CATCH per-section or whole batch, log, and rethrow or set job fail — silent evaluate = blind boards

What not to do

[ ] Empty CATCH blocks
[ ] COMMIT in CATCH “to save what we can” without knowing XACT_STATE
[ ] Swallow then RETURN 0 so Agent shows success
[ ] Rely only on client try/catch with procs that leave open tran
[ ] Mega-tran around entire evaluate of thousands of rows without batching strategy

Read procs

Usually no explicit tran. Optional:

BEGIN TRY
  SELECT …;
END TRY
BEGIN CATCH
  THROW;
END CATCH

Enough to standardize logging later; don’t over-engineer GetBoard.

Common failure patterns

Symptom	Likely cause
“Transaction count after EXECUTE…”	Missing rollback / nested tran mismatch
Job green but data wrong	CATCH swallowed error
Blazor timeout, partial ack	Long tran; no XACT_ABORT; client cancel
Uncommittable transaction	Errored mid-tran without rollback
Duplicate event rows	Retry without idempotent write

Tiny lab

Force a FK failure inside TRY with XACT_ABORT ON — confirm rollback + THROW
Log one row to ProcErrorLog then rethrow
Agent step: confirm failure shows in history when THROW fires

Tie-ins

SQL-12: Skeleton includes TRY/CATCH for writes
SPIKE-07: Ack/Evaluate must not leave torn Alert + AlertEvent
SQL-10: Job failure → operator / runbook

Done when

[ ] Write-proc template with XACT_ABORT + XACT_STATE + THROW adopted
[ ] No empty CATCH in warehouse procs
[ ] This note linked from Knowledge Base list

SQL Dude — SQL-15 T-SQL Error Handling v1