SQL语句中带有in语句的大小写问题

I ve following simple case statement

我有以下简单的案例陈述。

 CASE WHEN @SPOID is not null THEN 
            (176)
       ELSE
            (SELECT designationID from setupdesignations where reportto = 99)
       END

When else clause of this statement execute it gives me the following error

当这个语句的else子句执行时，它会给我以下错误

Subquery returned more than 1 value. This is not permitted when the subquery follows =, !=, <, <= , >, >= or when the subquery is used as an expression.

I am using this statement in the IN clause I was expecting multiple result to be returned in else case but it is giving me the above error. If I remove the case statement and execute the else part in query then i get the expected result

我在in子句中使用了这个语句，我希望在else情况下返回多个结果，但它给了我上面的错误。如果我删除case语句并执行query中的else部分，就会得到预期的结果

4 个解决方案

#1

You can't use a subselect as an expression within a CASE match, it expects a single value. Instead how about;

不能在CASE匹配中使用子select作为表达式，它需要一个值。而不是如何;

where 
  (@SPOID is not null and infield = 176)
or
  (@SPOID is null and infield in (SELECT designationID from setupdesignations where reportto = 99))

#2

Although you are using IN clause, the CASE statement only allows one row as return statement.

虽然您正在使用IN子句，但是CASE语句只允许一行作为返回语句。

You'll have to figure another way to do it.

你得想个别的办法。

#3

You could try using a dynamic query to achieve you end goal. The following link might point you in the right direction - check the last post: http://forums.devx.com/showthread.php?t=150247

您可以尝试使用动态查询来实现您的最终目标。下面的链接可能会指向正确的方向——请查看最后一篇文章:http://forums.devx.com/showthread.php?

#4

Try changing your in clause to:

试着将in条款改为:

... in 
(SELECT designationID 
 from setupdesignations 
 where reportto = 99 and @SPOID is not null
 UNION
 SELECT 176 where @SPOID is null)

#1