SQL Server 2005:从 WHERE 子句调用存储过程 [英] SQL Server 2005: Call a stored procedure from a WHERE clause

问题描述

我需要在 WHERE 子句中调用存储过程进行 SELECT.应该是这样的....

I need to make a SELECT with a call of a stored procedure in the WHERE clause. It should be something like that....

选择不同的前 10 名 i.x, d.droitFROM v_droit d, v_info iWHERE d.nomdroit='yy'并且 i.id<>2AND (select val from (exec up_droits(i.x, d.droit)) <>3

SELECT distinct top 10 i.x, d.droit FROM v_droit d, v_info i WHERE d.nomdroit='yy' AND i.id<>2 AND (select val from (exec up_droits(i.x, d.droit)) <>3

但它不起作用...有什么想法吗?

But it does not work... Any idea?

不要说用函数替换存储过程，因为不可能在函数中使用现有代码.所以这个函数不是一个有效的选项.我真的需要能够使用存储过程

Don't say to replace the stored procedure with a function because is not possible to use the existing code in a function. So the function is not a valid option. I really need to be able to use a stored procedure

推荐答案

这是通过首先执行存储过程，将输出捕获到 #temp 表或 @tabel 变量中，然后针对该表运行查询来实现的.像这样:

This is achieved by first executing the stored procedure, capturing the output into a #temp table or a @tabel variable, then running your query against the table. Something like this:

declare @droits_table (val ,... );
insert into @droits_table
exec up_droits(param, param);
SELECT distinct top 10 i.x, d.droit FROM v_droit d, v_info i WHERE d.nomdroit='yy' AND i.id<>2 AND (select val from @droits) <>3

当然这对您不起作用，因为 up_droits 需要查询中的 i.x 和 d.droit 参数.这表明您的存储过程可能应该是一个视图或表值函数.

Of course this will not work for you because the up_droits needs the i.x and d.droit parameters from the query. This indicates that your stored procedure should probably be a a view or table valued function.

这篇关于SQL Server 2005:从 WHERE 子句调用存储过程的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！