how to call a stored procedure in where clause of SQL

Question

I have a T-SQL script, requirment is I need to call a stored procedure in where clause. This stored procedure accept a parameter and returns a bit result. Kindly guide me how to do it.

Thanks

Edit: I cant modify this sp and make it a function. please

Solution 1

You can not use Stored Procedure in where clause but can use User Defined Function in where clause.

If you cant convert SP to function then you have to first get bit value from executing SP and use that variable in where clause..

Solution 2

You can use temporary table to store output of stored procedure and use it in where clause. The number of columns in your temporary variables must be same as that in resultset from procedure and with exact datatype as of columns in stored procedure resultset. eg,

create table #spResult ({columns as result of your sp})

insert into #spResult exec YourSP ({input parameters})

select * from yourtable 
where col in (select col from #spResult) 

drop table #spResult