我正在编写一个“简单”的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]) )更多推荐
发布评论