Variables scope which are defined within a while b

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

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