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?

Solution 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 :-)

Solution 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

Solution 3

Try this for fun

if 1 = 0
begin
  -- will never happen
  declare @xx int
end  
else  
begin
  set @xx = 1
end  
print @xx

Apparently the declare code does not have to be executed. Only be declared before it is used.

This don't work

if 1 = 0
begin
  -- will never happen
  set @xx = 1
end  
else  
begin
  declare @xx int
end  
print @xx

Solution 4

From Declare:

The scope of a local variable is the batch in which it is declared.

There are no more "local" scoping rules in T-SQL. It also means that you can't declare the same variable name inside IF and ELSE blocks.

All Declare does is declare a variable. It has no relation to assignment. The value of any variable that has never been assigned to is NULL. But thereafter, the only way the variables value will become NULL again is through an explicit assignment.

If you need it to be NULL at the top of each loop iteration, therefore, you must explicitly assign it.

Solution 5

In T-SQL a WHILE..END is not individually scoped, you can for example SELECT @insidevalue after the WHILE's END.

View more solutions