Filtering a result set of a stored procedure

Question

I have a stored procedure that returns a single result set. I would like to be able to name it and filter the resulting strings, for example:

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

Is there any way to do this?

Answer 1

There are several ways to solve this problem. The easiest way is to modify the stored procedure so that you can directly filter the result set, but I assume that for some reason you cannot do this.

What you need to do is save the results of the stored procedure in the table table / temp as follows:

 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 code: ... 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 don't completely understand your saved process. The temporary table here is actually not the most efficient solution and may be a bit of a hassle, but if it works for you, then go with it, but I have problems understanding the situation when you cannot just change your saved process on use a subquery instead of the temp table.