Select columns from result set of stored procedure

 

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

    • 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

    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. 

    CREATE TABLE #Result
(
  ID int,  Name varchar(500), Revenue money
)
INSERT #Result EXEC RevenueByAdvertiser '1/1/10', '2/1/10'
SELECT * FROM #Result ORDER BY Name
DROP TABLE #Result

    Source:
    http://stevesmithblog.com/blog/select-from-a-stored-procedure/

    链接地址: http://www.djcxy.com/p/24742.html

    上一篇: 函数与SQL Server中的存储过程

    下一篇: 从存储过程的结果集中选择列