使用 SQL 过滤存储过程的结果
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/2567141/
Warning: these are provided under cc-by-sa 4.0 license. You are free to use/share it, But you must attribute it to the original authors (not me):
StackOverFlow
Use SQL to filter the results of a stored procedure
提问by Ben McCormack
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.
但是，这种方法行不通。什么是实现查询存储过程返回数据的目标的好做法，最好无需查看原始存储过程的代码并对其进行修改。
采纳答案by Patrick Karcher
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:
SELECT * FROM udf_who2() WHERE login='bmccormack'If you really can't touch your procedure, and musthave 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.
将过程切换到用户定义的函数。在全世界，今天，人们正在制作应该是函数的存储过程。是教育问题。你的情况就是一个很好的例子。如果您的过程是 UDF，则您只需执行以下操作，正如您直觉上认为应该能够做到的那样：
SELECT * FROM udf_who2() WHERE login='bmccormack'如果您真的无法触及您的程序，并且必须在 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+ 优化了该查询；如果在 上有索引login，则在查询执行计划中看不到表扫描；非常有效率。
Edit: I should add that the innards of a UDF are similar to that of a SP. If it's messing with the logicof 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 modifiesanything in addition to returning results; UDFs cannot modify data in the db.
编辑：我应该补充一点，UDF 的内部结构类似于 SP 的内部结构。如果它弄乱了您想要避免的 SP的逻辑，您仍然可以将其更改为函数。有几次，我采用了我不想理解的大而可怕的过程代码，并成功地将其转移到了一个函数中。唯一的问题是程序是否除了返回结果之外还修改了任何内容；UDF 不能修改数据库中的数据。
回答by Petr Dostál
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
回答by bryanjonker
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.
传统上，将其添加到临时变量/表将起作用。
回答by Fahad
Place the data in a Table variable or Temp table and filter on it.
将数据放在表变量或临时表中并对其进行过滤。
回答by Lei Chi
OPENROWSET() is the way:
OPENROWSET() 是这样的：
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
