筛选存储过程的结果集 [英] Filtering a Stored Procedure's Result Set

问题描述

我有一个存储过程，该过程返回单个结果集. 我希望能够调用它并过滤结果行，例如:

I've got a stored procedure that returns a single result set. I would like to be able to call it and filter the resulting rows, something like:

SELECT * FROM (CALL sproc()) AS sp WHERE sp.someField = 0;

有没有办法做到这一点?

Is there a way to do this?

推荐答案

有两种方法可以解决此问题.最简单的方法是修改存储过程，以允许您直接过滤结果集，但由于某些原因，我假设您无法执行此操作.

There are a couple of ways to solve this. The easiest would be modifying the stored procedure to allow you to filter the result set directly but I'm assuming for some reason you are unable to do this.

然后您需要做的就是将存储过程的结果存储在表/临时表中，如下所示:

What you'll need to do then is store the results of the stored procedure in a table / temp table like so:

DECLARE @tablevar table(col1,..
INSERT INTO @tablevar(col1,..) exec MyStoredProc 'param1', 'param2'

SELECT col1, col2 FROM @tablevar WHERE col1 = 'abc'

如果您可以编辑子查询:

If you can edit the subquery:

旧存储过程: ... 选择 * 从 我的桌子 在哪里 col1 = @ param1 AND Col2 = @ param2

Old Stored Proc: ... SELECT * FROM MyTable WHERE Col1 = @param1 AND Col2 = @param2

新存储的过程:

....
SELECT
*
FROM
   (SELECT
      *
   FROM
      MyTable
   WHERE
      Col1 = @param1 AND
      Col2 = @param2
   ) a
WHERE
   Col3 = FilterRule1

但是也许我不完全了解您在此处存储的过程.这里的临时表实际上并不是性能最高的解决方案，可能有点麻烦，但是如果它对您有用，那么就去解决它，但是我在设想无法仅将存储的proc修改为使用子查询而不是临时表.

but maybe I'm not understanding your stored proc here completely. A temp table here isn't really the most performant solution and can be somewhat of a hassle but if it works for you then go with it, but I'm having trouble envisioning a situation where you couldn't just modify your stored proc to use a sub-query instead of a temp table.

这篇关于筛选存储过程的结果集的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！