Recently I needed to filter the results of a Stored Procedure, After some searching I found 2 options for this as below.
1. Using OpenRowSet Command
SELECT * FROM OPENROWSET (‘SQLOLEDB’, ‘Server=ServerName;TRUSTED_CONNECTION=YES;’, ‘EXEC sp_Who2’)
Remember in this method this feature should be enabled in the server.
2. Using Temporary Tables.
— Creating a temporary table.
CREATE TABLE #tblSPWho2
(SPId INT, Status NVARCHAR(200), LoginName NVARCHAR(200), HostName NVARCHAR(200), BlockedBy NVARCHAR(200), DBName NVARCHAR(200),
Command NVARCHAR(200), CPUTime BIGINT, DiskIO BIGINT, LastBatch NVARCHAR(200), ProgramName NVARCHAR(200), SPId2 INT, RequestId NVARCHAR(200))
— Inserting the results of the Stored Procedure into the temporary table.
INSERT INTO #tblSPWho2
EXEC sp_Who2
— Doing the required filtering using the temporary table.
SELECT * FROM #tblSPWho2 WHERE LoginName = ‘Domain\UserName’
— Removing the temporary table.
DROP TABLE #tblSPWho2
SP_Who2 Stored Procedure will bring all the processes that is currently active in SQL server with the relevant information, the above query will filter the results and will only display the processes that are initiated by the given username (Domain\UserName).
Arjuna's space 