SQL - WHERE 中的 CASE 表达式 [英] SQL - CASE expression inside WHERE

问题描述

我在这里阅读了有关在 WHERE 子句中使用 CASE 表达式的信息:

I read about using the CASE expression inside the WHERE clause here:

http://scottelkin.com/sql/using-a-case-statement-in-a-sql-where-clause/

我正在尝试根据用户应用程序将传入的合同编号，使用它来过滤来自我的 select 语句的结果.无论传入什么，我的代码当前都会抛出无效参数"错误.我验证了 SELECT/FROM 工作正常，就像没有 CASE 表达式的 WHERE 子句一样.这是我的代码.

I'm trying to use this to filter results from my select statement, based on a contract number which will be passed in by the user's application. My code currently throws an error of 'Invalid parameter' no matter what is passed in. I verified SELECT/FROM are working fine, as where as a WHERE clause without a CASE expression. Here is my code.

WHERE     (CASE WHEN @ContractNo = 0 THEN @ContractNo ELSE @ContractNo END =
tblContracts.ContractNo)

代码的冗余是为了排除故障，我打算稍后在 CASE 上使用通配符过滤.我现在专注于降低语法.我相信这应该返回参数与存储在表中的合同号匹配的所有记录.任何帮助或建议将不胜感激.

The redundancy of the code is for troubleshooting purposes, I'm planning on using wildcard filtering on the CASE later. I'm focusing on getting the syntax down right now. I believe this should return all records for which the parameter matches the contract number stored in the table. Any help or advice would be greatly appreciated.

推荐答案

阅读您的解释后，有一个更好的方法可以不用 CASE:

After reading your explanation, there's a better way to do this without CASE:

WHERE @ContractNo = 0 OR tblContracts.ContractNo = @ContractNo

这将只返回匹配的合同编号，除非 @ContractNo 为 0，在这种情况下它将返回所有记录.

This will return only matching contract numbers, unless @ContractNo is 0, in which case it will return all records.

我刚刚注意到 casperOne 提出了同样的问题.我没有看到.壮大自己.

I've just noticed that casperOne proposed the same thing. I didn't see that. Big up yourself.

这篇关于SQL - WHERE 中的 CASE 表达式的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！