title: Scope of table variable within SQL cursor
body: <p>If I run  the below in MS SQL 2008 R2 I get an unexpected result.</p>
  
  <pre><code>create table #DataTable (someID varchar(5))
  insert into #DataTable 
  values ('ID1'),('ID2'),('ID3'),('ID4'),('ID5')
  
  declare @data varchar(8);
  
  declare myCursor cursor for
  select someID from #DataTable
  
  open myCursor
  FETCH NEXT FROM myCursor INTO
  @data
  
  WHILE(@@Fetch_Status &gt;=0)
  BEGIN 
  
      declare @tempTable table (someValue varchar(10))
  
      insert into @tempTable select @data + '_ASDF'
      select * from @tempTable    
  
  FETCH NEXT FROM myCursor INTO
  @data
  
  END
  
  close myCursor
  deallocate myCursor
  
  drop table #DataTable
  </code></pre>
  
  <p>Result of the last iteration:</p>
  
  <pre><code>someValue
  ID1_ASDF
  ID2_ASDF
  ID3_ASDF
  ID4_ASDF
  ID5_ASDF
  </code></pre>
  
  <p>I haved expected only to see</p>
  
  <pre><code>someValue
  ID5_ASDF
  </code></pre>
  
  <p>It seems that the table variable @tempTable is kept in scope between cursor iterations - but how is it then possible to re-declare the variable in each iteration ? Makes no sense to me.</p>
  
  <p>I solved it by </p>
  
  <pre><code>delete @tempTable
  </code></pre>
  
  <p>in each iteration - which also backs up my assumption about it still being in scope.</p>
  
  <p>Can anyone explain this behavior ? </p>