Select columns from result set of stored procedure

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(col1,..
insert into @tablevar(col1,..) exec MyStoredProc 'param1', 'param2'

SELECT col1, col2 FROM @tablevar

Answer 2

WITH SprocResults AS
(
    EXEC sproc
)
SELECT Col1, Col2 FROM SprocResults

Answer 3

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.

Answer 4

Out of curiosity, does this query work:

SELECT * FROM EXEC MyStoredProc 'param1', 'param2'

If so, what column names does it display in the result set, and can you use those column names in your select list?

Answer 5

(Assuming SQL Server)

The only way to work with the results of a stored procedure in T-SQL is to use the INSERT INTO ... EXEC syntax. That gives you the option of inserting into a temp table or a table variable and from there selecting the data you need.

Answer 6

There is a way to do this (though not pretty!):

http://blogs.technet.com/wardpond/archive/2005/08/01/408502.aspx

Answer 7

This example uses the OpenRowSet command...

http://community.aspenware.com/blogs/johnmarquez/archive/2008/05/20/sql-server-openrowset-command.aspx

Answer 8

try this

use mydatabase
create procedure sp_onetwothree as
select 1 as '1', 2 as '2', 3 as '3'
go
SELECT a.[1], a.[2]
FROM OPENROWSET('SQLOLEDB','myserver';'sa';'mysapass',
    'exec mydatabase.dbo.sp_onetwothree') AS a
GO

Answer 9

It might be helpful to know why this is so difficult. A stored procedure may only return text (print 'text'), or may return multiple tables, or may return no tables at all.

So something like SELECT * FROM (exec sp_tables) Table1 will not work

Answer 10

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/