SQL: how to predicate over stored procedure's result sets?

Question

Simple question I couldn't figure out (not a SQL expert... sorry): I want to do a select on the result set of sp_who2. How can I?

for ex. select SPID from [result set from sp_who2]

Answer 1

From here (But requires Ad Hoc Distributed Queries enabled)

SELECT SPID,
        STATUS,
        Login,
        HostName,
        BlkBy,
        DBName,
        Command,
        CPUTime,
        DiskIO,
        LastBatch,
        ProgramName
   INTO #MyHead
   FROM OPENROWSET('SQLOLEDB',
   'Server=yourserverinstancehere;Trusted_Connection=Yes;Database=Master', 
                              'Set FmtOnly OFF; EXEC dbo.sp_Who2')

 SELECT * FROM #MyHead

Or From here (But you may need to adjust the columns depending upon the version of SQL Server you are on)

CREATE TABLE #sp_who3 
( 
    SPID INT, 
    Status VARCHAR(32) NULL, 
    Login SYSNAME NULL, 
    HostName SYSNAME NULL, 
    BlkBy SYSNAME NULL, 
    DBName SYSNAME NULL, 
    Command VARCHAR(32) NULL, 
    CPUTime INT NULL, 
    DiskIO INT NULL, 
    LastBatch VARCHAR(14) NULL, 
    ProgramName VARCHAR(32) NULL, 
    SPID2 INT 
) 

INSERT #sp_who3 EXEC sp_who2 

Depending upon what version of SQL Server you are on you might be better off using the Dynamic Management Views though.

Answer 2

You would have to insert the results into a table, a temporary table or a table variable.

CREATE TABLE #sp_who2 (etc...)

INSERT INTO #sp_who2 EXECUTE sp_who2

SELECT * FROM #sp_who2 WHERE whatever...

Another trick is using OPENROWSET

Answer 3

Use a loopback query like this

    SELECT SPID, Status, Login, HostName, BlkBy,DBName, 
   Command, CPUTime, DiskIO, LastBatch, ProgramName
        INTO #TempSpWho2
        FROM OPENROWSET ('SQLOLEDB','Server=(local);TRUSTED_CONNECTION=YES;',
      'set fmtonly off exec master.dbo.sp_who2')

    SELECT * FROM #TempSpWho2

see also Store The Output Of A Stored Procedure In A Table Without Creating A Table

on SQL Server 2005 and up use sys.dm_exec_sessions and sys.dm_exec_requests