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

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

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

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