It ran fine for months, then started corrupting data. The cause was two T-SQL behaviors I thought I understood.
We had a batch procedure that resolved a value for each item and wrote it to a permanent table, and a few downstream tables copied from there. It worked for a long time, then it started writing wrong values. The wrong values weren't random: each bad row held the value belonging to the row processed just before it.
No error, no exception. The procedure looked correct on every read-through. The cause was two T-SQL behaviors that Microsoft documents and that I'd misunderstood:
A variable declared inside a loop is created and set to NULL once for the whole batch, not once per iteration.
SELECT @var = column leaves the variable alone when the query returns no rows.
