Querying the resultset of a stored proc

Question

Is it possible to query the result set of a stored proc?

I only have execute permission on a stored proc and when I run the stored proc, it shows millions of rows. I need to query the result set of the stored proc.

What would be the efficient / simplest way?

FYI,I use SQL Server 2012.

this may help http://stackoverflow.com/questions/1492411/sql-server-select-from-stored-procedure — Massimiliano Peluso, May 23 '13 at 15:20
If adhoc distributed queries are enabled you can use [`OPENROWSET`](http://stackoverflow.com/a/3005435/73226). The most efficient way would be to write a query that only returns exactly the data you need though. — Martin Smith, May 23 '13 at 15:21
Martin, I don't have access to the tables. I've to query from the stored proc result. — nsivakr, May 23 '13 at 15:22
Well the other option if openrowset doesn't work is to `insert ... exec` the results into a table variable or temp table then select from that. — Martin Smith, May 23 '13 at 15:24

score 2 · Answer 1 · answered May 23 '13 at 16:18

If you know what the results look like, then you can put them into a table. First create the table and then use exec() or exec sp_executesql.

For example:

declare @lines table (id int identity(1, 1) primary key, line varchar(8000));

insert into @lines(line)
    exec sp_executesql N'sp_helptext ''information_schema.tables''';

select *
from @lines
order by id;

Querying the resultset of a stored proc

1 Answers1