title: How do I combine result sets from two stored procedure calls?
body: <p>I have a following stored procedure</p>
  
  <pre><code>CREATE PROCEDURE [dbo].[MyStored]
     @state int
  AS
     SELECT blahblahblah WHERE StoredState=@state LotsOfJoinsFollow;
  RETURN 0
  </code></pre>
  
  <p>and I'd like to call that stored procedure with @state being <code>0</code> and <code>1</code> and have the result sets returned by both calls combined with <code>UNION</code> semantics so that I have a new resultset that has rows from both the first call and the second call.</p>
  
  <p>Something like (imaginary SQL):</p>
  
  <pre><code>(EXEC MyStored 0) UNION (EXEC MyStored 1);
  </code></pre>
  
  <p>How do I achieve that?</p>