title: While loop in Microsoft SQL Server 2008
body: <p>I'm trying to come up with a simple example of a while loop using Microsoft SQL Server.
  Here I'm looping through all the tables that begin with the word temp.</p>
  
  <pre><code>Declare @Object_ID int,@Name varchar(50)
  set @Object_ID = 0
  while exists (
      select * from 
      sys.tables
      where type = 'u' 
      and object_ID &gt; @Object_ID 
      and Name like 'Temp%' 
  ) BEGIN
      select top 1 @Object_ID=Object_ID,@Name=Name
      from sys.tables
      where type = 'u' 
      and object_ID &gt; @Object_ID 
      and Name like 'Temp%' 
      order by Object_ID
      exec('Select ''' + @Name + ''' as TableName,count(*) AS Counter from ' + @Name)
  END
  </code></pre>
  
  <p>My problem is: now that I've looped through the tables, how do I use the information I've gathered with my exec command?
  In other words, can I stored the table that is returned from the exec command into a variable?</p>