Wednesday, 3 September 2008

Declare a local variable in T-Sql while loop

I have had some trouble to find a bug inside a sql server stored procedure.

I have a while loop in the stored procedure like this:

While Exists(…)
Begin
Declare @CategoryID int

// here I assign the value to @CategoryID with a CategoryID field by joining Category and Product tables, the join could return one record or no record with some where clause.

// then check value @CategoryID by printing it

// do something to make the while loop end
End

There is no problem with while loop itself, it ends as expected. The problem is the value of @CategoryID variable. I assume each time I declare the variable in the loop, a null value is assigned it by default. But the reality is not, if there is a value returned from the table join select, the value is assigned to the variable, this is how it should be. But if there is no value returned from the join, the value from the last loop is carried forward, instead of a null. This makes me thinking if the Declare command in a while loop only happen once and leave the variable available for the next loop.