title: Stored Procedure return multiple result sets
body: <p>I need a SP to return multiple sets of results. The second set of results would be based on a column of the first set of results.</p>
  
  <p>So:</p>
  
  <pre><code>declare @myTable1 table(field0 int,field1 varchar(255))
  insert into @myTable1 select top 1 field0, field1 from table1
  
  declare @myTable2 table(field0 int,field3 varchar(255))
  insert into @myTable2 
  select field0, field3 from table2 
  where @myTable1.field0 = @myTable2.field0
  </code></pre>
  
  <p>How do return @myTable1 and @myTable2 with my SP? Is this syntax even right at all?</p>
  
  <p>My apologies, I'm still a newbie at SQL...</p>
  
  <p><strong>EDIT:</strong></p>
  
  <p>So, I'm getting an error on the last line of the code below that says: "Must declare the scalar variable "@myTable1""</p>
  
  <pre><code>declare @myTable1 table(field0 int,field1 dateTime)
  insert into @myTable1 
  select top 1 field0, field1 
  from someTable1 m
  where m.field4 &gt; 6/29/2009
  
  select * from @myTable1
  select *
  from someTable2 m2
  where m2.field0 = @myTable1.field0
  </code></pre>
  
  <p>If I highlight and run the code up until the second <code>select *</code> it works fine...
  when I highlight the rest it acts like the first variable doesn't exist...</p>
  
  <p><strong>EDIT2:</strong>
  Figured that problem out. Thanks guys.</p>
  
  <pre><code>declare @myTable1 table(field0 int,field1 dateTime)
  insert into @myTable1 
  select top 1 field0, field1 
  from someTable1 m
  where m.field4 &gt; 6/29/2009
  
  select * from @myTable1
  select *
  from someTable2 m2
  where m2.field0 = (select field0 from @myTable1)
  </code></pre>