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.TypeFROM OPENROWSET ('SQLOLEDB','Server=(local);TRUSTED_CONNECTION=YES;','set fmtonly off exec master.dbo.sp_who')AS whoJOIN OPENROWSET ('SQLOLEDB','Server=(local);TRUSTED_CONNECTION=YES;','set fmtonly off exec master.dbo.sp_lock')AS locksON who.spid = locks.spid
A couple of notes: