Caveman's Blog

My commitment to learning.

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

References:
1. OPENQUERY – MSDN Online

About these ads

One Response

Subscribe to comments with RSS.

  1. Good Post!! I Used 2 method!!

    Carlo

    September 18, 2013 at 9:03 am


Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s

Follow

Get every new post delivered to your Inbox.

%d bloggers like this: