sql - 错误 156 严重性 15 状态 1 使用语法 CASE WHEN ELSE END

Question

当 @Keyword 从存储过程中声明时，我有新的 WHERE 条件。但是当我尝试 CASE 语法时，出现错误“关键字‘LIKE’附近的语法不正确。”

我在 PostgreSQL 中尝试过这种 CASE 语法，但没有错误。

ALTER PROCEDURE [dbo].[ACC_SEARCH_INVOICE_DATEX]
@projectID  varchar(3),
@FromDate DATETIME,
@Todate DATETIME ,
@Filter varchar(50),
@Keyword varchar(50),
@posted int,

@status integer,
@source varchar(50)
AS

SELECT posted, InvoiceHead.InvoiceID, InvoiceHead.Date AS date, InvoiceHead.UserID, InvoiceHead.CreditTerm, InvoiceHead.DueDate, InvoiceHead.CustomerID, 
Party.Name, InvoiceHead.Total, InvoiceHead.Currency, InvoiceHead.Rate, Subtotal, Discount, Tax, Commision, Bonus, Total, DownPayment, Balance ,
substring(InvoiceID,4,LEN(invoiceid)-11-3) Source, selling, technician, Lock , InvoiceHead.Status

FROM InvoiceHead LEFT OUTER JOIN Party ON InvoiceHead.CustomerID = Party.PartyID  

WHERE 

CASE
 WHEN @Keyword = 'SCP' 
    THEN ((InvoiceHead.InvoiceID LIKE '%' + @Keyword + '%') or (InvoiceHead.InvoiceID LIKE '%SCJ%'))
 WHEN  @Keyword = 'SCNH' 
    THEN ((InvoiceHead.InvoiceID LIKE '%' + @Keyword + '%') or (InvoiceHead.InvoiceID LIKE '%SNJH%'))
 WHEN  @Keyword = 'SCNHL' 
    THEN ((InvoiceHead.InvoiceID LIKE '%' + @Keyword + '%') or (InvoiceHead.InvoiceID LIKE '%SNJHL%'))
 ELSE ((InvoiceHead.InvoiceID LIKE '%' + @Keyword + '%'))
END

ORDER BY   InvoiceHead.InvoiceID

标签： sqlsql-serverswitch-statement

Answer 1

你应该重构条件：

WHERE 
(CASE
 WHEN @Keyword = 'SCP'   AND ((InvoiceHead.InvoiceID LIKE '%' + @Keyword + '%') or (InvoiceHead.InvoiceID LIKE '%SCJ%'))   THEN 1
 WHEN @Keyword = 'SCNH'  AND ((InvoiceHead.InvoiceID LIKE '%' + @Keyword + '%') or (InvoiceHead.InvoiceID LIKE '%SNJH%'))  THEN 1
 WHEN @Keyword = 'SCNHL' AND ((InvoiceHead.InvoiceID LIKE '%' + @Keyword + '%') or (InvoiceHead.InvoiceID LIKE '%SNJHL%')) THEN 1
 WHEN ((InvoiceHead.InvoiceID LIKE '%' + @Keyword + '%')) THEN 1
END) = 1