Select from Stored Procedure

Stored procedures are often used to encapsulate business rules, especially for reporting purposes. However, it frequently happens that there is a need to use those encapsulated business rules (rather, the record sets that they produce) as input to a separate query. In order to do this, we need to be able to SELECT from the stored procedure… which cannot be done natively.

If the output structure of the stored procedure is known and stable, we can create a temporary table with the same structure as the output from the stored procedure and then INSERT INTO the temporary table. To demonstrate this, first we will define a stored procedure that returns a record set. We will use the utfNumbers function to populate our result set.

-- Create a stored procedure that returns a result set
CREATE PROCEDURE dbo.uspReturnRecords
    -- SET NOCOUNT ON added to prevent extra result sets from
    -- interfering with SELECT statements.

    -- Select the records to return
    SELECT n
    FROM DB.dbo.utfNumbers(10)

Given this stored procedure with a known structure, we now create a temporary table to hold the records.

-- Create a temporary table of the known structure
    n BIGINT)

-- Insert into a temporary table
EXECUTE DB.dbo.uspReturnRecords

Now, we simply select from the temporary table. (And drop it when finished. Though not strictly necessary, it’s always good practice to clean up after yourself!)

-- Select from the temporary table

-- Drop the temporary table

So far, so good. But what if the structure of the stored procedure result set is complex, variable, or entirely unknown? Given those options the preceding method is impractical.

To address those potential challenges, we can simply use the OPENQUERY functionality. The only complication with this approach is that OPENQUERY takes as its first parameter the name of a server (or linked server). Thus, we will first define a reflexive linked server and then use it to query our stored procedure via OPENQUERY.

-- If a linked server of the same name already exists, drop it
IF EXISTS(SELECT * FROM master..sysservers WHERE srvname = N'mirror')
    EXECUTE sp_dropserver N'mirror'

-- Create a reflexive linked server
EXECUTE sp_addlinkedserver
    @server = N'mirror'
    , @srvproduct = N''
    , @provider = N'SQLOLEDB'
    , @datasrc = @@SERVERNAME --current server is data source for linked server

-- Select the stored procedure into a temporary table
FROM OPENQUERY(mirror, 'EXECUTE DB.dbo.uspReturnRecords;')

-- Select from the temporary table

-- Clean up by dropping the temporary table

While we could also drop the reflexive linked server as part of the clean-up, it’s handy to have around, so why not leave it? Better yet, add it to your development server and production server build sheet so it’s always there when you need it. 🙂

Post a comment or leave a trackback: Trackback URL.


  • By Query Optimizer FAIL! 001 | T-SQL Ref on 03 Apr 2014 at 2048

    […] Next we’ll create a reflexive linked server. Using a linked server is a contributing factor in tripping up the optimizer, but we need not actually be talking to a second server. Thus we’ll create a linked server that refers back to itself, allowing us to demonstrate the issue on a single server. I generally create such a connection on all of my DEV databases. For other uses of a reflexive linked server, see my previous post on selecting from stored procedures. […]

Leave a Reply

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

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

Google+ photo

You are commenting using your Google+ 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 )


Connecting to %s

%d bloggers like this: