Variables scope which are defined within a while block in stored procedures - SQl Server

Question

I've come across a interesting scenario (at least for me) in a stored procedure. Would like to have experts opinion and thoughts on it.

DECLARE @loopcounter INT SET @loopcounter=10  WHILE @loopcounter > 0 BEGIN   DECLARE @insidevalue int    IF (@loopcounter%2 = 0)   SET @insidevalue = @loopcounter    PRINT 'Value_' + CAST(@insidevalue AS NVARCHAR) + '_'    SET @loopcounter = @loopcounter - 1 END 

I was expecting this block will give the output as below

Value_10_  Value_ _  Value_8_  Value_ _  Value_6_  Value_ _  Value_4_  Value_ _  Value_2_  Value_ _ 

Instead I got output as below:

Value_10_

Value_10_

Value_8_

Value_8_

Value_6_

Value_6_

Value_4_

Value_4_

Value_2_

Value_2_

I thought if I declare a variable inside a while block, then for every iteration it will reset the value to NULL or default value (from c# background).

If this is by design then my question is how does SQLServer treat 'DECLARE' statement for that variable inside while block? Does it ignore it as the variable is already in memory?

Can somebody please explain me this behavior?

Answer 1

The variable scope is the whole batch in this case a stored procedure.

It isn't re-declared every loop

So this is exactly as expected

Edit:

There is a recent blog article which is quite similar. The author was quickly corrected :-)

Answer 2

From Transact-SQL Variables

The scope of a variable is the range of Transact-SQL statements that can reference the variable. The scope of a variable lasts from the point it is declared until the end of the batch or stored procedure in which it is declared.

The DECLARE is not in itself an executable statement. The variable declarations are all identified at compile time and the memory reserved for them in the execution context.

If you use the 2008+ Declare and Set syntax. The Set part of the statement will occur every loop iteration however.

DECLARE @loopcounter INT SET @loopcounter=10  WHILE @loopcounter > 0 BEGIN   DECLARE @insidevalue INT = NULL    IF (@loopcounter%2 = 0)   SET @insidevalue = @loopcounter    PRINT 'Value_' + CAST(@insidevalue AS NVARCHAR) + '_'    SET @loopcounter = @loopcounter - 1 END