If you ever had to “fix a batch” of invoices, reset workflow statuses, or reprocess failed documents, you know the pain, one mistake and you either update too much, or you update the right rows but lose the evidence of what changed.
SQL Server has a few features that make these scripts safer, easier to review, and much easier to debug later. Below is a clean demo that you can paste in SSMS and use in your daily work.
SET NOCOUNT ON;
SET XACT_ABORT ON;
BEGIN TRY
BEGIN TRAN;
/* 1) Demo table (drop + create) */
IF OBJECT_ID('dbo.DocumentRepositoryDemo', 'U') IS NOT NULL
DROP TABLE dbo.DocumentRepositoryDemo;
CREATE TABLE dbo.DocumentRepositoryDemo
(
DocumentRepositoryID bigint IDENTITY(1,1) NOT NULL CONSTRAINT PK_DocumentRepositoryDemo PRIMARY KEY,
OriginalInvoiceID varchar(50) NOT NULL,
WorkflowStatus int NOT NULL,
HasResponse bit NOT NULL,
IsForwardedTP bit NOT NULL,
ModifiedAtUtc datetime2(3) NULL,
ModifiedBy sysname NULL
);
/* 2) Seed some fake data */
INSERT dbo.DocumentRepositoryDemo
(OriginalInvoiceID, WorkflowStatus, HasResponse, IsForwardedTP, ModifiedAtUtc, ModifiedBy)
VALUES
('7601002721400-318242', 2, 1, 1, SYSUTCDATETIME(), SUSER_SNAME()),
('7601002721400-318161', 3, 1, 0, SYSUTCDATETIME(), SUSER_SNAME()),
('7601001392038-318127', 4, 0, 1, SYSUTCDATETIME(), SUSER_SNAME()),
('7601000777447-967851', 9, 0, 0, SYSUTCDATETIME(), SUSER_SNAME()),
('7601000777447-968159', 1, 1, 1, SYSUTCDATETIME(), SUSER_SNAME());
/* 3) Target list (table variable) */
DECLARE @InvoiceIds TABLE
(
OriginalInvoiceID varchar(50) NOT NULL PRIMARY KEY
);
INSERT INTO @InvoiceIds (OriginalInvoiceID)
VALUES
('7601002721400-318242'),
('7601002721400-318161'),
('7601000777447-968159');
/* 4) Change tracking (audit table variable) */
DECLARE @Changed TABLE
(
DocumentRepositoryID bigint,
OriginalInvoiceID varchar(50),
OldWorkflowStatus int,
NewWorkflowStatus int,
OldHasResponse bit,
NewHasResponse bit,
OldIsForwardedTP bit,
NewIsForwardedTP bit,
ChangedAtUtc datetime2(3),
ChangedBy sysname
);
/* 5) Update + capture before/after with OUTPUT */
UPDATE dr
SET dr.WorkflowStatus = 5,
dr.HasResponse = 0,
dr.IsForwardedTP = 0,
dr.ModifiedAtUtc = SYSUTCDATETIME(),
dr.ModifiedBy = SUSER_SNAME()
OUTPUT
deleted.DocumentRepositoryID,
deleted.OriginalInvoiceID,
deleted.WorkflowStatus, inserted.WorkflowStatus,
deleted.HasResponse, inserted.HasResponse,
deleted.IsForwardedTP, inserted.IsForwardedTP,
inserted.ModifiedAtUtc,
inserted.ModifiedBy
INTO @Changed
(
DocumentRepositoryID,
OriginalInvoiceID,
OldWorkflowStatus, NewWorkflowStatus,
OldHasResponse, NewHasResponse,
OldIsForwardedTP, NewIsForwardedTP,
ChangedAtUtc,
ChangedBy
)
FROM dbo.DocumentRepositoryDemo dr
INNER JOIN @InvoiceIds i
ON i.OriginalInvoiceID = dr.OriginalInvoiceID
WHERE dr.WorkflowStatus <> 5
OR dr.HasResponse <> 0
OR dr.IsForwardedTP <> 0;
/* 6) Results */
SELECT *
FROM @Changed
ORDER BY OriginalInvoiceID, DocumentRepositoryID;
SELECT COUNT(*) AS RowsUpdated
FROM @Changed;
COMMIT TRAN
END TRY
BEGIN CATCH
IF XACT_STATE() <> 0
ROLLBACK TRAN
THROW
END CATCH
What makes this “SQL Server cool” (and why it matters)
SQL Server normally prints messages like “(1 row affected)” after every statement. That is fine in ad hoc work, but noisy in scripts that do multiple steps.
With SET NOCOUNT ON:
- result sets stay clean (only the output you intentionally select),
- less chatter between server and client,
- stored procedures often become easier to consume from apps and jobs.
SET XACT_ABORT ON
With XACT_ABORT ON, when a runtime error happens (conversion, constraint violation, etc.), SQL Server forces the transaction into a state where it cannot silently continue. In practice, this reduces the chance you end up with a transaction left open, or partially executed logic that you forget to undo.
It pairs perfectly with TRY/CATCH because it pushes you toward “all or nothing”.
3) TRY/CATCH + XACT_STATE(), predictable rollback logic
XACT_STATE() helps because it tells you the transaction state:
1 there is an active, committable transaction
-1 the transaction is uncommittable, you must rollback
0 no active transaction
That is why this pattern is so common:
IF XACT_STATE() <> 0
ROLLBACK TRAN;
4) Table variables as “scoped in memory tables”
This script uses two table variables:
@InvoiceIds is a safe target list
@Changed is an audit collector
Why that’s useful:
- you avoid long IN (...) lists inside the update,
- your target list can be validated and deduped (the PRIMARY KEY
prevents duplicates),
- you keep the update set based, readable, and reviewable.
Important detail for accuracy: table variables are often called “in memory”, but they can spill to tempdb depending on size and query plan. Still, they are great for small and medium sets.
5) OUTPUT deleted, inserted, instant before/after audit
This is one of the most underrated features for operational work.
OUTPUT gives you:
- deleted.* values before the update
- inserted.* values after the update
So you can:
- show exactly what changed,
- store it into @Changed, optionally persist
- it into a real audit table.
This is the difference between “I ran a script” and “I can prove exactly what the script did”.
Takeaways :
NOCOUNT keeps scripts readable and reduces noise.
XACT_ABORT makes failure behavior safer.
TRY/CATCH plus XACT_STATE() gives you consistent rollback behavior.
Table variables make targeting and audit capture clean and scoped.
OUTPUT gives you real auditing with almost no extra effort.
Avoiding unnecessary updates is a performance win and a safety win.
