SQL Server CASE子句在括号内的位置

I have this problem in my SQL code:

我的SQL代码中有这个问题:

I will only show my WHERE clause, because it is a bit long, this is it:

我只会显示我的WHERE子句,因为它有点长,这就是它:

where 
 ((@account_status = 1027 AND a.AccountStatus = 1027  AND a.FolioNo = 
  @folio_no AND b.ReservationNo = @reservation_id)) OR
 ((@account_status = 1026 AND a.AccountStatus = 1026  AND a.FolioNo = 
 @folio_no AND b.ReservationNo = @reservation_id)) OR
 ((@account_status = 1025 AND a.AccountStatus = 1025 AND @trans_code = 1 AND 
 a.AccountStatementTransCode = 1 AND b.FolioNo = @folio_no AND 
 b.ReservationNo = @reservation_id)) OR
 ((@account_status = 1025 AND a.AccountStatus = 1025 AND @trans_code != 1 
 AND a.AccountStatementTransCode != 1 AND b.FolioNo = @folio_no AND 
 b.ReservationNo = @reservation_id)) OR
 ((a.FolioNo = @folio_no AND b.ReservationNo = @reservation_id AND 
 @trans_code = 2 AND a.AccountStatementTransCode = 2 AND 
 case
   when @sub_category = 14 then i.category_id is null
    else i.category_id = @sub_category 
 end )) OR
 ((a.FolioNo = @folio_no AND b.ReservationNo = @reservation_id AND 
 @trans_code = 3 AND a.AccountStatementTransCode = 3 AND i.category_id = 
 @sub_category)) OR
 ((a.FolioNo = @folio_no AND b.ReservationNo = @reservation_id AND 
 @trans_code = 4 AND a.AccountStatementTransCode = 4 AND i.category_id = 
 @sub_category)) OR
 ((a.FolioNo = @folio_no AND b.ReservationNo = @reservation_id AND 
 @trans_code = 5 AND a.AccountStatementTransCode = 5 AND i.category_id = 
 @sub_category)) OR
 ((a.FolioNo = @folio_no AND b.ReservationNo = @reservation_id AND 
 @trans_code = 6 AND a.AccountStatementTransCode = 6 AND i.category_id = 
 @sub_category))

I want that if input is @sub_category = 14 then it will return the category with NULL values, else it will return the @sub_category values. How can I do that ?

我希望如果输入是@sub_category = 14那么它将返回具有NULL值的类别,否则它将返回@sub_category值。我怎样才能做到这一点 ?

3 个解决方案

#1

Replace CASE expression logic with below

用下面替换CASE表达式逻辑

AND 
1 = CASE WHEN @sub_category = 14 AND i.category_id is null 
         THEN 1
         WHEN @sub_category <> 14 AND i.category_id = @sub_category 
         THEN 1
         ELSE 0
    END

#2

Change your CASE to:

将您的CASE更改为:

case
    when @sub_category = 14 then null
    else i.category_id = @sub_category 
 end

#3

TRY THIS: Simple way, If your category_id never hold -1 or you can use the value that will never be in category_id i.e. 0,-1

试试这个:简单的方法,如果你的category_id永远不会保持-1或你可以使用永远不会在category_id中的值,即0,-1

AND ISNULL(i.category_id, -1) = CASE WHEN @sub_category = 14 THEN 
                                    -1 
                                ELSE @sub_category END

#1