Select columns from result set of stored procedure
select, sql-server, stored-procedures, t-sql
Solution
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
Problem
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'.