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
AS
BEGIN
    -- SET NOCOUNT ON added to prevent extra result sets from
    -- interfering with SELECT statements.
    SET NOCOUNT ON
    ;

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

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
CREATE TABLE #TEMPTABLE (
    n BIGINT)
;

-- Insert into a temporary table
INSERT INTO #TEMPTABLE
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
SELECT *
FROM #TEMPTABLE
;

-- Drop the temporary table
DROP TABLE #TEMPTABLE
;

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'
;
GO

-- 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
;
GO

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

-- Select from the temporary table
SELECT *
FROM #TEMPTABLE
;

-- Clean up by dropping the temporary table
DROP TABLE #TEMPTABLE
;

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. 🙂

Advertisements
Post a comment or leave a trackback: Trackback URL.

Trackbacks

  • 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:

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

%d bloggers like this: