Skip to main content

Posts

Showing posts from February, 2018

Google Search

Custom Search

Live Search

Yahoo Search


Search the web Search this site

Filtering Results of a Stored Procedure

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 CommandSELECT * 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.��…