Ward Pond's SQL Server blog

Ruminating on issues pertinent to the design and development of sound databases and processes under Microsoft SQL Server 2008, SQL Server 2005, and SQL Server 2000 (while reserving the right to vent about anything else that's on my mind)

Blogs

The OPENROWSET Trick: Accessing Stored Procedure Output In A SELECT Statement

  • Comments 19
  • Likes

Updated 20 Mar 2009: This code is revisited here.

This nifty little trick will allow you, with some limitations, to treat the output of a stored procedure as a named SQL record set rather than creating a table and going through an INSERT.. EXEC process.  The output of the stored procedure is then available for direct manipulation in SELECT statements, JOINs, etc.

I’ve passed on this nugget many times since it was first shared with me about five years ago.  I’ve tried to find the person who came up with it first, I’ve lately heard from someone who published it in a newsgroup back in 1999.  A shy individual, this person wants no part of my efforts to publicly recognize the genius behind this approach (my favorite comment from a colleague, upon seeing this, was “That's sick!  In a twisted, useful, and instructive way, of course!”)

This syntax works in both SQL Server 2000 and SQL Server 2005, and requires integrated security to be turned on.  Under SQL Server 2005, Ad Hoc Distributed Queries must be enabled.

Here’s a simple sample that assigns the output from master.sp_who to a derived table called tbl:

SELECT  *
FROM    OPENROWSET ('SQLOLEDB','Server=(local);TRUSTED_CONNECTION=YES;','set fmtonly off exec master.dbo.sp_who')
AS tbl

Here’s a slightly more complex (but perhaps ultimately silly) example that joins the output from two stored procedures:

SELECT  who.loginame AS LoginName,
        who.HostName, 
        DB_NAME(locks.dbid) AS DatabaseName, 
        locks.Type
FROM    OPENROWSET ('SQLOLEDB','Server=(local);TRUSTED_CONNECTION=YES;','set fmtonly off exec master.dbo.sp_who')
AS  who
JOIN    OPENROWSET ('SQLOLEDB','Server=(local);TRUSTED_CONNECTION=YES;','set fmtonly off exec master.dbo.sp_lock')
AS  locks
ON  who.spid = locks.spid

A couple of notes:

  • The ‘set fmtonly off’ is included only for completeness.  If you’re certain the FMTONLY will always be set to OFF, it’s safe to omit this (if FMTONLY is set ON, the calls will produce only metadata, no results).
  • The OPENROWSET call opens a separate connection to SQL Server, so there is some overhead associated with this approach.  For this reason, I’d avoid using this technique in an OLTP system as it’s unlikely to scale well (don’t run with scissors).
  • Using this technique with a poorly architected stored procedure could lead to blocking issues.
  • This technique is not supported inside a declared transaction.
Comments
  • A quick-and-dirty solution for importing file-based XML documents into SQL Server tables is discussed.

  • All I did was ask a question, honest..
    When I was vetting my recent post regarding database design issues,...

  • Thank you so much for sharing this.  I've been looking for this solution for a long time.  I really appriceiate it.

  • Nice - does it work with parameters too?

  • One of the most popular posts in the history of this little corner of the Internets is one from August,

  • sweet...exactly what I was looking for, and yes it does work with parameters too.

    I only need this to debug so not worried about performance.

    xxooxxoo

Your comment has been posted.   Close
Thank you, your comment requires moderation so it may take a while to appear.   Close
Leave a Comment