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??
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
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.
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
If you want to load the table variable each time the loop executes. DROP FROM @Tablevariable once work done within the loop.