错误156严重性15使用语法CASE WHEN ELSE END状态1
错误156严重性15使用语法CASE WHEN ELSE END状态1
提问于 2019-06-15 18:20:42
当@Keyword为declare from Store Procedure时，我有新的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回答 3
Stack Overflow用户
回答已采纳
发布于 2019-06-15 18:55:36
您可以在不使用case的情况下构建此逻辑。我会推荐以下几点：
WHERE InvoiceHead.InvoiceID LIKE '%' + @Keyword + '%') OR
(@Keyword = 'SCP' AND InvoiceHead.InvoiceID LIKE '%SCJ%') OR
(@Keyword = 'SCNH' AND InvoiceHead.InvoiceID LIKE '%SNJH%') OR
(@Keyword = 'SCNHL' AND InvoiceHead.InvoiceID LIKE '%SNJHL%');SQL Server没有布尔类型。出现错误，因为CASE表达式返回的是布尔表达式，但这在SQL Server中不是有效值。
一般来说，与引入CASE相比，将布尔运算符用于WHERE条件会更简单。
Stack Overflow用户
发布于 2019-06-15 18:22:38
您应该重新定义条件：
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) = 1Stack Overflow用户
发布于 2019-06-15 18:29:28
你可以像下面这样使用
WHERE (@Keyword = 'SCP' AND ((InvoiceHead.InvoiceID LIKE '%' + @Keyword + '%') or (InvoiceHead.InvoiceID LIKE '%SCJ%')))
OR (@Keyword = 'SCNH' AND ((InvoiceHead.InvoiceID LIKE '%' + @Keyword + '%') or (InvoiceHead.InvoiceID LIKE '%SNJH%')))
OR (@Keyword = 'SCNHL' AND ((InvoiceHead.InvoiceID LIKE '%' + @Keyword + '%') or (InvoiceHead.InvoiceID LIKE '%SNJHL%')))页面原文内容由Stack Overflow提供。腾讯云小微IT领域专用引擎提供翻译支持
原文链接：
https://stackoverflow.com/questions/56609430
复制相关文章
相似问题
腾讯云开发者
