Filtering a Stored Procedure's Result Set

Question

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?

Answer 1

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'

EDIT: If you can edit the subquery:

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

New Stored Proc:

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

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.