Local Variables in a T-Sql loop are not really local

I found this the hard way out. Local variables in a T-Sql loop in Sql Server are not really local in the strict terms of a block-oriented programming language like C/C++.

e.g. In C++, if you have the following:

int i;
for (i = 0; i<=10; i++)
{
       int *p = NULL;
       if (p == NULL)
          p = new int(1);
       else
          *p = *p + 1;
       printf("%d", *p);

}

You expect to see 1 being printed 10 times. (I am ignoring the memory leaks above. This is just a sample situation).

Now try the follwing in Sql Server:

DECLARE @i int;

set @i = 1;

WHILE @i<=10
BEGIN
    DECLARE @j int;
    
    IF @j IS NULL
        set @j = 1;
    ELSE
        set @j = @j + 1;
    
    SELECT @j;
    
    set @i = @i + 1;
END

To your surprise, the output is 1,2,3... 10. Clearly, @j preserved its value from the previous iteration, even when it was declared inside the loop.

I got stuck in this gotcha, when I declared a local table variable inside a loop. I expected the table variable to start empty at the beginning of each iteration. However, to my (rather inpleasant) surprise, it was still holding rows from previous iterations.

The solution was simple. Declare the table variable above the loop, and clear it out (using DELETE FROM...) at the beginning of each loop iteration.