根据where子句中的参数从表中选择(Select from table depending on a parameter in where clause)

根据where子句中的参数从表中选择(Select from table depending on a parameter in where clause)

我正在编写一个“简单”的SQL函数来选择数据，具体取决于WHERE子句中的参数：

DECLARE @csp AS CHAR(1) = 'F' SELECT [NUMERO_DOSSIER] FROM [dbo].[Dossier] WHERE [CSP] IN ( CASE @csp WHEN 'F' THEN (SELECT [CSP] FROM [dbo].[CSP_1]) WHEN 'N' THEN (SELECT [CSP] FROM [dbo].[CSP_2]) END )

根据SQL Server 2012，此查询没有语法错误。但运行它将返回此错误：

消息512，级别16，状态1，行2子查询返回多个值。 当子查询遵循=，！=，<，<=，>，> =或当子查询用作表达式时，这是不允许的。

据我了解，这里的问题是： CASE函数返回标量，并且不能返回表。

我怎样才能重写它以使其工作？

I'm a writing a 'simple' SQL function that selects data, depending on a parameter in the WHERE clause :

DECLARE @csp AS CHAR(1) = 'F' SELECT [NUMERO_DOSSIER] FROM [dbo].[Dossier] WHERE [CSP] IN ( CASE @csp WHEN 'F' THEN (SELECT [CSP] FROM [dbo].[CSP_1]) WHEN 'N' THEN (SELECT [CSP] FROM [dbo].[CSP_2]) END )

This query has no syntax error according to SQL Server 2012. But running it returns this error :

Msg 512, Level 16, State 1, Line 2 Subquery returned more than 1 value. This is not permitted when the subquery follows =, !=, <, <= , >, >= or when the subquery is used as an expression.

To my understanding, the problem here is : the CASE function returns scalars, and can't return a table.

How can I rewrite this to make it work ?

最满意答案

不要在WHERE子句中使用CASE - 它通常会使逻辑更加复杂。 只需将逻辑扩展为布尔逻辑：

WHERE ( @csp = 'F' AND [CSP] IN (SELECT [CSP] FROM [dbo].[CSP_1]) ) OR ( @csp = 'N' AND [CSP] IN (SELECT [CSP] FROM [dbo].[CSP_2]) )

Don't use CASE in a WHERE clause -- it usually makes the logic more complicated. Just expand the logic as boolean logic:

WHERE ( @csp = 'F' AND [CSP] IN (SELECT [CSP] FROM [dbo].[CSP_1]) ) OR ( @csp = 'N' AND [CSP] IN (SELECT [CSP] FROM [dbo].[CSP_2]) )