如何在SQL的where子句中调用存储过程

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.

我有一个T-SQL脚本，要求是我需要在where子句中调用存储过程。此存储过程接受参数并返回位结果。请指导我如何做到这一点。

Thanks

谢谢

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

编辑：我无法修改此sp并使其成为一个函数。请

2 个解决方案

#1

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

您不能在where子句中使用存储过程，但可以在where子句中使用用户定义函数。

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..

如果你不能将SP转换为函数，那么你必须首先从执行SP获取位值并在where子句中使用该变量。

#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,

您可以使用临时表来存储存储过程的输出，并在where子句中使用它。临时变量中的列数必须与过程的结果集中的列数相同，并且与存储过程结果集中的列的精确数据类型相同。例如，

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

#1