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.
