使用 SQL 过滤存储过程的结果 [英] Use SQL to filter the results of a stored procedure
问题描述
我在 Stack Overflow 上查看了与此问题相关的其他问题，但似乎没有一个能清楚地回答这个问题.
I've looked at other questions on Stack Overflow related to this question, but none of them seemed to answer this question clearly.
我们有一个名为 sp_who2 的系统存储过程，它返回服务器上所有正在运行的进程的信息结果集.我想过滤存储过程返回的数据；从概念上讲，我可能会这样做:
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.
推荐答案
没有好的方法可以做到这一点.这是存储过程的限制.您的选择是:
There are no good ways to do that. It is a limitation of stored procedures. Your options are:
将过程切换到用户定义函数.在全世界，今天，人们正在制作应该是函数的存储过程.是教育问题.你的情况就是一个很好的例子.如果您的过程是 UDF，则您只需执行以下操作，就像您直觉上认为的那样:
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'
如果您真的无法触及您的程序，并且必须在 sql 中完成此操作，那么您将不得不变得时髦.制作另一个存储过程来包装原始过程.在新过程中调用现有过程并将值放入临时表中，然后使用所需的过滤器对该表运行查询，并将结果返回给外界.
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 Server 2005 开始，用户定义的函数是您封装数据检索的方式.存储过程以及视图是在特定情况下使用的专用工具.它们在正确的时间都非常方便，但不是首选.有些人可能认为上面的示例 (A) 获取函数的所有结果，然后 (B) 过滤该结果集，就像子查询一样.事实并非如此.SQL Server 2005+ 优化了该查询；如果login 上有索引，则查询执行计划中看不到表扫描；效率很高.
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.
编辑:我应该补充一点，UDF 的内部结构类似于 SP 的内部结构.如果它扰乱了您想要避免的 SP 的逻辑，您仍然可以将其更改为一个函数.有几次，我采用了我不想理解的大而可怕的过程代码，并成功地将它转移到了一个函数中.唯一的问题是过程是否修改除了返回结果之外的任何内容；UDF 不能修改数据库中的数据.
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.
这篇关于使用 SQL 过滤存储过程的结果的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！
