I ran into some unexpected behavior with local variables that I think should be pointed out. Normally, local variables can only be declared once in a query. For example, this code:
DECLARE @TestInt INT DECLARE @TestInt INT
produces this error:
Msg 134, Level 15, State 1, Line 2
The variable name ‘@TestInt’ has already been declared. Variable names must be unique within a query batch or stored procedure.
However, it seems when you declare a local variable in a loop, it can be declared multiple times without errors. Furthermore, you might assume that each new declaration would cause any values stored in that variable to be reset. After all, Microsoft says: “After declaration, all variables are initialized as NULL, unless a value is provided as part of the declaration.” This is not the case for local variables declared repeatedly in a loop. Consider the following code:
SET NOCOUNT ON DECLARE @loop INT SET @loop = 1 WHILE @loop <=3 BEGIN DECLARE @TestInt INT SET @TestInt = ISNULL(@TestInt,0) + 1 SELECT @TestInt SET @loop = @loop +1 END
This code will loop three times, with each loop declaring the variable, setting it to a value, and then selecting it. The output is below.
As you can see, the repeated declarations do not cause an error nor do they cause the contents of the variable to be reset. I can understand the query engine not throwing an error as it complies the statement – after all, without executing the code, it doesn’t know how many times the loop will the executed. However, I would have expected the engine to throw an error at runtime. At the very least, I would have expected the variable value to be reset to null with each declaration. Neither of these events happen.