SQL Server CASE 子句位于括号内的 where

Question

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

我只会显示我的 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))

我希望如果输入是@sub_category = 14，那么它将返回带有NULL值的类别，否则它将返回@sub_category > 值(value)观。我怎样才能做到这一点？

Answer 1

将 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