Caveman's Blog

My commitment to learning.

Posts Tagged ‘SQL openquery

SQL Server: How to select data from a stored procedure?

with one comment

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 [1] 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?

The select statement with OPENQUERY executed successfully. At this point we should be able to select all or specific columns from the resultset of the stored procedure like in the example below:

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