Use Results of Stored Procedure in Where clause

Question

I have a Stored Procedure(SP) in MS SQL 2008 R2 I'm building that requires a list of users to narrow down the data returned.

The system I'm working on has a GetUsers SP which returns a list of users, and I need to then use this to limit the results returned from the SP I am working on myself.

I've had a look at TABLE variables, but not sure if this is quite what I need.

How would I go about integrating the results of one SP into the where clause of another SP?

Answer 1

You can take the results of your SP by

DECLARE @yourtablevariable TABLE (fields....)    

INSERT INTO @yourtablevariable
EXEC GetUsers

Then

SELECT *
FROM othertable
    INNER JOIN @yourtablevariable users on othertable.userid=users.userid

Or

SELECT *
FROM othertable
WHERE userid in (SELECT UserID FROM @yourtablevariable)

Alternatively, if possible, you could convert GetUsers to a table valued function.

Answer 2

In SQL Server if the SP returns a TABLE, than the best way to use a table variable (@Table) or memory table (#Table) if the returned amount of data is bigger.