SQL: how to predicate over stored procedure's result sets?
Simple question I couldn't figure out (not a SQL expert... sorry): I want to do a select 开发者_JAVA技巧on the result set of sp_who2. How can I?
for ex. select SPID from [result set from sp_who2]
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
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
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.
精彩评论