Select columns from result set of stored procedure

Question

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

SELECT col1, col2 FROM EXEC MyStoredProc 'param1', 'param2' 

When I used the above syntax I get the error:

"Invalid Column Name".

I know the easiest solution would be to change the stored procedure, but I didn't write it, and I can't change it.

Is there any way to do what I want?

• 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.

• I tried using WITH SprocResults AS .... as suggested by Mark, but I got 2 errors
  

  Incorrect syntax near the keyword 'EXEC'.
  Incorrect syntax near ')'.

• I tried declaring a table variable and I got the following error
  

  Insert Error: Column name or number of supplied values does not match table definition

• If I try
  SELECT * FROM EXEC MyStoredProc 'param1', 'param2'
  I get the error :
  

  Incorrect syntax near the keyword 'exec'.

Answer 1

Can you split up the query? Insert the stored proc results into a table variable or a temp table. Then, select the 2 columns from the table variable.

Declare @tablevar table(col1 col1Type,.. insert into @tablevar(col1,..) exec MyStoredProc 'param1', 'param2'  SELECT col1, col2 FROM @tablevar 

Answer 2

Here's a link to a pretty good document explaining all the different ways to solve your problem (although a lot of them can't be used since you can't modify the existing stored procedure.)

How to Share Data Between Stored Procedures

Gulzar's answer will work (it is documented in the link above) but it's going to be a hassle to write (you'll need to specify all 80 column names in your @tablevar(col1,...) statement. And in the future if a column is added to the schema or the output is changed it will need to be updated in your code or it will error out.