T-SQL Loop Bug Silently Corrupted Data for Months Due to Two Misunderstood Behaviors

A batch SQL Server procedure ran correctly for months before it began writing wrong values to a permanent table and its downstream copies. Investigation revealed that each corrupted row was carrying over the value from the previously processed row, with no errors or exceptions raised. Two T-SQL behaviors were responsible: unlike C++ or Java, a variable declared inside a T-SQL loop is initialized only once per batch, not per iteration, and a SELECT assignment leaves a variable unchanged when the query returns no rows. Together, these behaviors caused missing rows to silently reuse the last valid value instead of returning NULL. The fix involves explicitly resetting the variable to NULL at the start of each loop iteration and using SET with a scalar subquery instead of SELECT-assignment to ensure a no-result lookup correctly returns NULL.