SQL Server Variable Scope inside loops, Not what U think if u come from C#/Java...

Guess what prints in this loop? 

Will @i b 1 every time ? Will @outTbl  only have one row?
Actually no, scope of loop variables is to the script, a Gotcha 4 ya!
If u use table variables in loop make sure u delete on each iteration to clear.!


Related:

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:

Results here...

set nocount on

declare @tt int
set @tt =10
while @tt>0
begin
-- scope to loop or scope to sript?
declare @i int
Declare @outTbl TABLE(Release_App_schedule_ID int)

insert into @outTbl
select  @tt
set @i = isnull(@i,0) + 1

select '@outTbl', * from @outTbl
select '@i', @i
   
    set @tt=@tt-1

end


IS SAME AS


set nocount on

declare @tt int
-- scope to loop or scope to sript?
declare @i int
Declare @outTbl TABLE(Release_App_schedule_ID int)


set @tt =10
while @tt>0
begin


insert into @outTbl
select  @tt
set @i = isnull(@i,0) + 1

select '@outTbl', * from @outTbl
select '@i', @i
   
    set @tt=@tt-1

end