SQL Server: How to select data from a stored procedure?
In his post I will show you two ways of using SQL to query the data that is generated by the execution of a stored procedure.
Method 1: Using OPENQUERY  to be able to select the intended data. OPENQUERY executes the specified pass-through query on the specified linked server. “getEmployees” stored procedure returns all the records from the employee table. OPENQUERY executes the “getEmployees” stored procedure in the Demo database and also allows us to execute a select command on the result set.
SELECT * FROM OPENQUERY ([SERVERNAME],'exec demo.dbo.getEmployees')
First time I ran the above query I got an error: Server ‘[SERVERNAME]’ is not configured for DATA ACCESS. We have to set the DATA ACCESS option of the SQL Server Instance to true, to be able to use OPENQUERY and that can be done as follows:
exec sp_serveroption @server = '[SERVERNAME]', @optname = 'DATA ACCESS', @optvalue = 'TRUE'
How you can find the SQL Server instance name?
Method 2: Using table variable to select the intended data. In this method we first create a table variable with a schema that matches the output of stored procedure execution. We then capture the output of the stored procedure execution in a table variable. At this point we will be able to scout through all the output data using DML.
declare @tempTable table(EmpID int, EmpName varchar(50), Salary int) insert into @tempTable exec demo.dbo.getEmployees select * from @tempTable
1. OPENQUERY – MSDN Online