Use SQL to filter the results of a stored procedure

Question

I've looked at other questions on Stack Overflow related to this question, but none of them seemed to answer this question clearly.

We have a system Stored Procedure called sp_who2 which returns a result set of information for all running processes on the server. I want to filter the data returned by the stored procedure; conceptually, I might do it like so:

SELECT * FROM sp_who2
WHERE login='bmccormack'

That method, though, doesn't work. What are good practices for achieving the goal of querying the returned data of a stored procedure, preferably without having to look of the code of the original stored procedure and modify it.

Solution 1

There are no good ways to do that. It is a limitation of stored procedures. Your options are:

1. Switch the procedure to a User Defined Function. All over world, today, people are making stored procedures that should be functions. It's an education issue. You situation is a good example why. If your procedure were instead a UDF, you could just do the following, exactly as you intuitively think you should be able to:

   SELECT * FROM udf_who2()
   WHERE login='bmccormack'
   

2. If you really can't touch your procedure, and must have this done in sql, then you'll have to get funky. Make another stored procedure to wrap your original procedure. Inside your new procedure call your existing procedure and put the values into a temporary table, then runs a query against that table with the filter you want, and return that result to the outside world.

Starting with SQL server 2005, user defined functions are how you encapsulate data retrieval. Stored Procedures, along with Views, are specialty tools to use in particular situations. They're both very handy at the right time, but not the first choice. Some might think that the above example (A) gets all the results of the function and then (B) filters on that resultset, like a subquery. This is not the case. SQL server 2005+ optimizes that query; if there is an index on login, you not see a table scan in the query execution plan; very efficient.

Edit: I should add that the innards of a UDF are similar to that of a SP. If it's messing with the logic of the SP that you want to avoid, you can still change it to a function. Several times I've taken large, scary procedures code that I did not want to have to understand, and successfully transferred it to a function. The only problem will be if the procedure modifies anything in addition to returning results; UDFs cannot modify data in the db.

Solution 2

The filtering of temporary table is the possible way.

-- Create tmp table from sp_who results
CREATE TABLE #TmpWho
(spid INT, ecid INT, status VARCHAR(150), loginame VARCHAR(150),
hostname VARCHAR(150), blk INT, dbname VARCHAR(150), cmd VARCHAR(150), request_id INT)
INSERT INTO #TmpWho
EXEC sp_who

-- filter temp table where spid is 52
SELECT * FROM #TmpWho
WHERE spid = 52

DROP TABLE #TmpWho

Solution 3

You can do an OPENROWSET(), but there are some security/performance issues involved.

SELECT * 
FROM OPENROWSET ('SQLOLEDB', 'Server=(local);TRUSTED_CONNECTION=YES;', 'exec mystoredproc')

Traditionally, adding it to a temp variable/table will work.

Solution 4

Place the data in a Table variable or Temp table and filter on it.

Solution 5

OPENROWSET() is the way:

SELECT *
FROM
    OPENROWSET('SQLNCLI', 'Server=(local);TRUSTED_CONNECTION=YES;', 'exec sp_who')
WHERE loginame = 'test' AND dbname = 'Expirement';

Also you need enable advance config before working:

sp_configure 'show advanced options', 1;  
RECONFIGURE;
GO
sp_configure 'Ad Hoc Distributed Queries', 1; 
RECONFIGURE;
GO 

View more solutions

Comments

[Cade Roux]

With the caveat that there is potentially a big difference between inline table-valued functions and multi-statement table-valued function. Inline TVFs can be optimized like views and they behavior a lot more closely to (parameterized) views than to functions.

[Patrick Karcher]

@Case Roux Very good point! If it's a multi-statement function, option 1 will increase simplicity but probably wouldn't increase performance. For a large amount of rows, could be a dog. If performance was important, Option 2 might be faster. Depends on the situation, as always.

[yucer]

This might not work if the stored procedure use temporary tables. Because user defined functions can not create temporary tables.