title: Select columns from result set of stored procedure
body: <p>I have a stored procedure that returns 80 columns, and 300 rows.  I want to write a select that gets 2 of those columns.  Something like </p>
  
  <pre><code>SELECT col1, col2 FROM EXEC MyStoredProc 'param1', 'param2'
  </code></pre>
  
  <p>When I used the above syntax I get the error: </p>
  
  <blockquote>
    <p>"Invalid Column Name".</p>
  </blockquote>
  
  <p>I know the easiest solution would be to change the stored procedure, but I didn't write it, and I can't change it.  </p>
  
  <p>Is there any way to do what I want?</p>
  
  <ul>
  <li><p>I could make a temp table to put the results in, but because there are 80 columns so I would need to make an 80 column temp table just to get 2 columns.  I wanted to avoid tracking down all the columns that are returned.</p></li>
  <li><p>I tried using <code>WITH SprocResults AS ....</code> as suggested by Mark, but I got 2 errors <BR></p>
  
  <blockquote>
    <p>Incorrect syntax near the keyword 'EXEC'.<BR>Incorrect syntax near ')'.</p>
  </blockquote></li>
  <li><p>I tried declaring a table variable and I got the following error <br></p>
  
  <blockquote>
    <p>Insert Error: Column name or number of supplied values does not match table definition</p>
  </blockquote></li>
  <li><p>If I try <BR>
  <code>SELECT * FROM EXEC MyStoredProc 'param1', 'param2'</code><br>
  I get the error :<br></p>
  
  <blockquote>
    <p>Incorrect syntax near the keyword 'exec'.</p>
  </blockquote></li>
  </ul>