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.
SQL Server scoping rules are per batch. BEGIN/END have no meaning on scope in this example.
That is, the variable is *not* re-declared once per loop. It is declared once in this batch.
MSDN (http://msdn.microsoft.com/en-us/library/ms188927.aspx) says “The scope of a local variable is the batch in which it is declared. “
T-SQL follows the Algol family scoping rules. The loop body is creatd only once, not over and over.
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 +=
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
Seems like everyone knows about this behavior except me :-)
You are not alone, even I got the same doubt and I’m a c# programmer, so that was obvious. Here is my question on Stackoverflow: http://stackoverflow.com/questions/6045952/variables-scope-which-are-defined-within-a-while-block-in-stored-procedures-sql/6046059#6046059
Shaun , This article is really very good because I solve a problem after reading your thoughts. Thanks.
DECLARE @TestInt INT =NULL
“THEN YOU WILL GET THE EXPECTED RESULT …WHY ?”
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
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.
if we declare variables again and again will it any effect on performance? suppose we have to declare 50 or more than that variables in loop?