Our SQL Server loop kept writing the previous row's value

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.

Either one on its own is survivable. Together they make a missing row reuse the last good answer. Here's how it played out, with a script you can run to watch it happen.

What the code looked like

WHILE (@cnt > 0)

BEGIN

    DECLARE @Value VARCHAR(100)

    SELECT @Item_Code = ITEM FROM @ItemList WHERE RowNo = @cnt



    SELECT @Value = isnull(p.ITEM_VAL, f.ITEM_VAL)

    FROM @Primary p

    LEFT JOIN @Fallback AS f ON p.ITEM_CODE = f.ITEM_CODE

    WHERE p.ITEM_CODE = @Item_Code

      AND p.ITEM_SEQ = (SELECT MAX(pmax.ITEM_SEQ) FROM @Primary pmax WHERE pmax.ITEM_CODE = @Item_Code)



    IF (@Value IS NULL) OR LTRIM(RTRIM(@Value)) = ''

    BEGIN

        SELECT TOP 1 @Value = ITEM_VAL FROM @Fallback WHERE ITEM_CODE = @Item_Code

    END



    UPDATE @Target SET ITEM_VAL = @Value WHERE ITEM_CODE = @Item_Code

    SET @cnt = @cnt - 1

END

Read top to bottom, the intent is plain: resolve from the primary table, fall back to the secondary one if nothing came back, write the result. The DECLARE @Value sitting inside the loop looks like it hands each pass a clean variable. That was the assumption that bit me.

A DECLARE inside a loop isn't a reset

In C++, Java or C#, a variable declared inside a loop body is a fresh binding every pass. T-SQL doesn't do that. A local variable's scope is the whole batch, and it's initialized to NULL when it's declared, not at the point in the code where you wrote DECLARE.

So putting DECLARE @Value inside the WHILE is just decoration. There's one @Value for the whole run, set to NULL once, and every iteration after the first inherits whatever the previous one left in it. That's also why SQL Server doesn't complain about a double declaration even though the DECLARE line is reached nine times; it isn't a statement that re-runs each pass.

A quick proof:

DECLARE @i INT = 0

WHILE @i < 3

BEGIN

    DECLARE @x INT

    SET @x = ISNULL(@x, 0) + 1

    PRINT @x

    SET @i = @i + 1

END

It prints 1, 2, 3. If the DECLARE reset @x each pass, you'd get 1, 1, 1.

Docs: .

How the two combine

Now the failure is just mechanics:

1. 
   @Value is never cleared between iterations.


2. For an item missing from the primary table, the resolve matches nothing and doesn't touch @Value.


3. The guard IF (@Value IS NULL) OR LTRIM(RTRIM(@Value)) = '' sees a leftover non-null value, decides resolution already worked, and skips the fallback.


4. The item gets written with the previous item's value.

The fallback that should have rescued the missing item never fires, because the stale value makes it look like there was nothing to rescue.

Fixing it

Two changes close the gap.

Clear the variable at the top of every iteration so a missing row can't carry the last value forward:

WHILE (@cnt > 0)

BEGIN

    SET @Value = NULL

    ...

END

And for a single-value lookup like this, prefer SET with a scalar subquery over SELECT-assignment. A scalar subquery that finds no rows gives you NULL, which is what you want here, because then the IS NULL guard does its job:

SET @Value = (

    SELECT isnull(p.ITEM_VAL, f.ITEM_VAL)

    FROM @Primary p

    LEFT JOIN @Fallback f ON p.ITEM_CODE = f.ITEM_CODE

    WHERE p.ITEM_CODE = @Item_Code

      AND p.ITEM_SEQ = (SELECT MAX(pmax.ITEM_SEQ) FROM @Primary pmax WHERE pmax.ITEM_CODE = @Item_Code)

)

It read fine to me for months. Each behavior is harmless on its own, which is why the pair survives review. If a loop variable can outlive its iteration, assume it does.