Jaa

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

  • Artikkeli

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

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

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

  • Anonymous
    January 01, 2003
    A quick-and-dirty solution for importing file-based XML documents into SQL Server tables is discussed.

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

  • Anonymous
    March 18, 2009
    Nice - does it work with parameters too?

  • Anonymous
    February 02, 2011
    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

  • Anonymous
    February 25, 2011
    This code does'nt work for me when i'm using an SP that has parameters!

  • Anonymous
    April 01, 2011
    DECLARE @P XML Declare @T table(X XML) INSERT INTO @T(X) EXEC sp_executesql N'      SELECT            1    as Tag,            NULL as Parent,            LogId AS [TABLE!1!LogId],            NULL AS [TR!2!OrderBy],            NULL AS [TR!2!Color],            NULL AS [TR!2!TD!ELEMENT],            NULL AS [TR!2!TD!ELEMENT],            NULL AS [TR!2!TD!ELEMENT],            NULL AS [TR!2!TD!ELEMENT]      FROM [Log].[Log] L      UNION ALL      SELECT            2 as Tag,            1 as Parent,            LI_LogId,            LogItemId,            CASE LI_Status WHEN ''E'' THEN ''Red'' ELSE ''Black'' END AS Color,            LogItemId,            LI_DT,            LI_AffectedDbObject,            LI_Msg      FROM      (            SELECT              L.Descr              ,LI.[LogItemId]              ,LI.[LogId] AS LI_LogId              ,CONVERT(NVARCHAR(40), LI.[DT], 121) AS LI_DT              ,LI.[Msg] AS LI_Msg              ,LI.[AffectedDbObject] AS LI_AffectedDbObject              ,LI.[Status] AS LI_Status            FROM [Log].[Log] L            LEFT JOIN [Log].[LogItems] LI ON LI.LogId = L.LogId            WHERE L.LogId = [Log].GetLastLogId()      ) A      ORDER BY [TR!2!OrderBy] ASC      FOR XML EXPLICIT, ROOT(''BODY''), TYPE ' SELECT X AS LogHtmlInfo FROM @T

  • Anonymous
    May 04, 2011
    Ha, thanks, I have been looking this all over the place this afternoon. I tried the parameters and it works too. Probably it is a 2008 thing, Donald. Thanks <a href="holiday.reviewinfobase.com/">Happy Mother's Day</a>

  • Anonymous
    October 31, 2011
    The comment has been removed

  • Anonymous
    December 14, 2014
    I have to to read data from FoxPro through openrowset and path will variable.please can you tell me how I can do that.

  • Anonymous
    January 03, 2016
    Thanks, your post help me!