使用SQL来过滤存储过程的结果

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

我已经研究了与这个问题相关的Stack Overflow上的其他问题，但似乎没有一个问题能清楚地回答这个问题。

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:

我们有一个名为sp_who2的系统存储过程，它为服务器上所有正在运行的进程返回一组信息。我想要过滤存储过程返回的数据;从概念上讲，我可以这样做:

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.

但这种方法行不通。为了实现查询存储过程的返回数据的目标，最好的做法是不需要查看原始存储过程的代码并修改它。

4 个解决方案

#1

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

没有什么好办法。这是存储过程的限制。你的选择是:

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:

将过程切换到用户定义的函数。如今，全世界的人们都在制作应该是函数的存储过程。这是一个教育问题。你的情况就是一个很好的例子。如果你的程序是UDF，你可以做以下的事情，就像你直觉上认为你应该能够做到的那样:
```
SELECT * FROM udf_who2()
WHERE login='bmccormack'
```
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.

如果你真的不能触摸你的程序，并且必须在sql中完成这个操作，那么你将不得不变得时髦。制作另一个存储过程来包装原始过程。在新过程中调用现有的过程并将值放入一个临时表中，然后用您想要的过滤器对该表执行查询，并将结果返回给外部世界。

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.

从SQL server 2005开始，用户定义函数是如何封装数据检索的。存储过程和视图是在特定情况下使用的专用工具。它们都在合适的时间非常方便，但不是首选。有些人可能认为上面的示例(A)获取函数的所有结果，然后(B)对结果集进行筛选，如子查询。事实并非如此。SQL server 2005+优化该查询;如果登录时有索引，则在查询执行计划中看不到表扫描;很能干的人。

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.

编辑:我应该补充一点，UDF的内部结构与SP的内部结构类似。如果它破坏了您想要避免的SP的逻辑，您仍然可以将它更改为一个函数。有几次，我使用了一些我不希望理解的大而可怕的程序代码，并成功地将其转换为一个函数。唯一的问题是如果程序除了返回结果之外还修改了任何内容;udf不能修改db中的数据。

#2

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

您可以执行OPENROWSET()，但是涉及到一些安全性/性能问题。

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

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

传统上，将它添加到临时变量/表中是可行的。

#3

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

#4

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

将数据放在表变量或临时表中，并对其进行筛选。

#1