Filtering Results of a Stored Procedure

Posted: February 15, 2018 in Development, SQL Server
Tags: , , ,

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).

Advertisements

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out /  Change )

Google+ photo

You are commenting using your Google+ account. Log Out /  Change )

Twitter picture

You are commenting using your Twitter account. Log Out /  Change )

Facebook photo

You are commenting using your Facebook account. Log Out /  Change )

w

Connecting to %s