Table variables inside while loop not initializing everytime : SQL Server

Question

I am wondering why the table variables inside while loop does not behave like other variables. Table variables created only once and will be used across through out whole looping. but other variables getting initialized every time when loop increases.

Check out the below code for more info

declare @tt int
set @tt =10
while @tt>0
begin

        declare @temptable table(id int identity(1,1),sid bigint)
        insert into @temptable 
                select @tt union all
                select @tt + 1 

                select * from @temptable 
               --delete from @temptable
                set @tt=@tt-1
end

is this a bug??

Romanov

Answer 1

Your premise is wrong. Other variables don't get reinitialised every time the declare statement is encountered either.

set nocount on

declare @tt int
set @tt =10
while @tt>0
begin

        declare @i int

        set @i = isnull(@i,0) + 1
        print @i
        set @tt=@tt-1

end

Prints

1
2
...
9
10

Illusage

thanks for the update.. i missed isnull when testing with variable.. that led to my cofusion.. :( – Romanov

Answer 2

As expected

SQL Server variable scope is per batch or the entire function/procedure/trigger, not per black/nested construct

http://msdn.microsoft.com/en-us/library/ms187953.aspx:

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.

Margarettmargaretta

Answer 3

Though it is old post just wann add my comments

set nocount on
declare @tt int
set @tt =10
while @tt>0
begin
        declare @i int=0
        set @i = @i + 1
        print @i
        set @tt=@tt-1
end

Results:
1
1
1
1
1
1
1
1
1
1

Lachish

What is that supposed to prove? You're reinitializing @i at the end of the declare – Adumbral

Answer 4

If you want to load the table variable each time the loop executes. DROP FROM @Tablevariable once work done within the loop.

Public