How to call a stored procedure in a where clause

Question

I have two stored procedures. Lets take them as SP1 and SP2. SP1 is the main Stored proc and SP2 is the stored proc called inside SP2. i.e) SP1 has three input parameters(Fromdate,todate,LoginName) and SP2 has one input parameter(LoginName). What I want to do is: Call the SP2 inside SP1's where clause as:

SELECT column1,
       column2,
       column3
from <Sometable>
where column1<=fromdate and column1>=todate and column2 in(exec SP2 @LoginName)

IAm not sure of the syntax but this is what I want to achieve. Help needed

Answer 1

Here's one such way of achieving this - you might also want to read up on Table-valued UDFs.

CREATE TABLE #logins (
  <schema for EXEC SP2 return here>
)

INSERT INTO #logins
  EXEC SP2 @LoginName

SELECT column1, column2, column3
from YOUMISSEDTHETABLEOUTHERE
where column1<=fromdate and column1>=todate and column2 in (
  SELECT therelevantcolumn FROM #logins
)