Local Variables Declared Repeatedly

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.

———–
1

———–
2

———–
3

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.

Share

10 thoughts on “Local Variables Declared Repeatedly

  1. The behavior you describe is correct, eventhough .net developers sometimes insist that T-SQL variables have “block scope” (sorry. it doesn’t). Newer versions of T-SQL do support initialization on the the DECLARE (declare @foo int = 1). It also supports increment operators such as +=

  2. Shaun,

    This is actually expected/anticipated behavior from a programming standpoint as MOST programming languages define the scope of variables within a loop as being scoped ONLY within that loop.

    There IS a (theoretical at this point – given the size of your loop) perf overhead for instantiating those variables over and over again – something that most .NET coders/etc know about and SQL Server is WEIRD/DIFFERENT in that you can actually access these loop-scoped variables OUTSIDE (or underneath) the loop.

    Otherwise, this is all as expected – perfectly normal.

    Note too that this is the same behavior you can get with cursors/etc (NOT that I’m advocating nested cursors).

    –Mike

  3. 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

    PRINT @Loop

    is the there a way i can print outside the loop i want to use the @loop value

    1. There is not. As written, your code would print “4” when the loop exits. If you need to print each loop value, that command should go inside the loop. The value of @Loop is available to the rest of the code once the looping stops, but it would contain the last value, in this case, 4.

Leave a Reply

Your email address will not be published. Required fields are marked *

I am a real person and can prove it by doing this math problem: (required) Time limit is exhausted. Please reload CAPTCHA.