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



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 (12)

  1. Anonymous says:

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

  2. Anonymous says:

    All I did was ask a question, honest..

    When I was vetting my recent post regarding database design issues,…

  3. Anonymous says:

    A quick-and-dirty solution for importing file-based XML documents into SQL Server tables is discussed.

  4. RON says:

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

  5. Giles Collingwood says:

    Nice – does it work with parameters too?

  6. chris haas says:

    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

  7. Donald says:

    This code does'nt work for me when i'm using an SP that has parameters!

  8. Alexey says:

    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

  9. Jen says:

    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>

  10. GPUToaster says:

    3 MAJOR IMPLICATIONS all in total:

    • CONNECTION STRING remains exposed, can be an easy hack point.

    • It could be used to connect a non-compromised to compromised server, allows external connections.

    • If external connection has problem such as buffer overflow, then the caller server will get compromised too.

  11. Vikash Gaurav says:

    I have to to read data from FoxPro through openrowset and path will variable.please can you tell me how I can do that.

  12. Mai Hai says:

    Thanks, your post help me!