在子句中使用CASE语句。

Is is possible to use a CASE statement inside an IN clause?

是否可以在IN子句中使用CASE语句?

This is a simplified version of what I have been trying to get to compile correctly:

这是我一直试图正确编译的一个简化版本:

SELECT * FROM MyTable 
WHERE StatusID IN (
CASE WHEN @StatusID = 99 THEN (5, 11, 13)
ELSE (@StatusID) END )

Thanks!

谢谢!

4 个解决方案

#1

CASE returns a scalar value only. You can do this instead. (I am assuming, as per your example, that when @StatusID = 99, a StatusID value of 99 is not a match.)

CASE只返回一个标量值。你可以这样做。(我假设，如您的示例所示，当@ sid = 99时，一个99的小雕像不匹配。)

select *
from MyTable
where (@StatusID = 99 and StatusID in (5, 11, 13))
    or (@StatusID <> 99 and StatusID = @StatusID)

#2

No. Instead, you can put it outside

不。相反，你可以把它放在外面

SELECT *
FROM MyTable
WHERE 1 = (CASE WHEN @StatusID = 99 and StatusId in (5, 11, 13) then 1
                WHEN coalesce(@StatusId, 0) <> 99 and StatusId in (@StatusID) then 1
                ELSE 0
           END)

You can also write this without the case statement.

你也可以不用case语句来写这个。

Another option is dynamic SQL, where you actually create a string with the SQL statement and then execute it. However, dynamic SQL seems like overkill in this case.

另一种选择是动态SQL，您实际使用SQL语句创建一个字符串，然后执行它。然而，在这种情况下，动态SQL似乎有些过头了。

#3

I thought I'd attempt this differently using a Table Valuue Constructor - are TVCs not allowed in the following context?

我想我可以用表值构造函数来尝试不同的方法——在下面的上下文中不允许TVCs吗?

SELECT * 
FROM MyTable  
WHERE StatusID IN 
   ( 
   SELECT 
       CASE 
         WHEN @StatusID = 99 THEN (values(5),(11),(13)) t(StatusID )
         ELSE @StatusID 
   END  
   )

#4

You can do this using TVCs, but the approach is a little different. It doesn't use case, but it will scale more nicely where there are a number of possible options to choose from:

你可以用TVCs来做，但是方法有点不同。它不使用用例，但是当有许多可能的选项可供选择时，它会更好地扩展:

SELECT * 
FROM MyTable  
join (values 
      (99,5),(99,11),(99,13),
      (@StatusID , @StatusID)    
    ) t(k,v) on t.k= @StatusID and t.v = StatusID)

or if you need everything in the where clause then:

或者如果你需要where子句中的所有东西:

SELECT * 
FROM MyTable  
WHERE exists (
    select 1
    from (values 
      (99,5),(99,11),(99,13),
      (@StatusID , @StatusID)    
    ) t(k,v)
    where t.k= @StatusID and t.v = StatusID)

#1