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