SQL 在预期条件的上下文中指定的非布尔类型的表达式，靠近“END”
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/3018549/
Warning: these are provided under cc-by-sa 4.0 license. You are free to use/share it, But you must attribute it to the original authors (not me):
StackOverFlow
An expression of non-boolean type specified in a context where a condition is expected, near 'END'
提问by Mike Keller
So maybe someone can point me in the right direction of what is causing this error? I've been fighting with this for a couple of hours and searching the web, and I can't figure out what I'm doing wrong here. It's included as part of a stored procedure, I don't know if that matters, if it does I can include that as well. Tables and field names have been changed to protect the innocent... meaning my job. Thanks.
所以也许有人可以为我指出导致此错误的正确方向？我已经为此奋斗了几个小时并在网上搜索，但我无法弄清楚我在这里做错了什么。它作为存储过程的一部分包含在内，我不知道这是否重要，如果确实如此，我也可以将其包含在内。表和字段名称已更改以保护无辜者……这意味着我的工作。谢谢。
SELECT
/* The fields are here*/
FROM
/* my joins are here */
WHERE
(Table.Field = stuff)
AND
(Table.Field2 = otherstuff)
AND
(Table2.Field3 = someotherstuff)
AND
CASE @param1
WHEN 0 THEN 'Table.Field IS NULL'
WHEN 1 THEN 'Table.Field2 IS NOT NULL'
ELSE ''
END
Thanks for the responses. Technically egrunin was the correct answer for this question, but OMG Ponies and Mark Byers were pretty much the same thing just missing that last piece. Thanks again.
感谢您的回复。从技术上讲，egrunin 是这个问题的正确答案，但 OMG Ponies 和 Mark Byers 几乎是一样的东西，只是缺少最后一部分。再次感谢。
回答by egrunin
I'm pretty sure the other answers leave out a case:
我很确定其他答案遗漏了一个案例：
WHERE
(Table.Field = stuff)
AND
(Table.Field2 = otherstuff)
AND
(Table2.Field3 = someotherstuff)
AND
(
(@param1 = 0 and Table.Field IS NULL)
OR
(@param1 = 1 and NOT Table.Field2 IS NULL)
OR
(@param1 <> 0 AND @param1 <> 1) -- isn't this needed?
)
回答by Mark Byers
You are returning a string from your case expression, but only a boolean can be used. The string is not evaluated. You could do what you want using dynamic SQL, or you could write it like this instead:
您正在从 case 表达式返回一个字符串，但只能使用布尔值。不评估字符串。你可以使用动态 SQL 做你想做的事，或者你可以这样写：
AND (
(@param1 = 0 AND Table.Field IS NULL) OR
(@param1 = 1 AND Table.Field IS NOT NULL)
)
回答by OMG Ponies
- You can't use CASE in the WHERE clause like you are attempting
- The text you provided in the CASE would only run if you were using dynamic SQL
- 您不能像尝试那样在 WHERE 子句中使用 CASE
- 您在 CASE 中提供的文本只会在您使用动态 SQL 时运行
Use:
用：
WHERE Table.Field = stuff
AND Table.Field2 = otherstuff
AND Table2.Field3 = someotherstuff
AND ( (@param1 = 0 AND table.field IS NULL)
OR (@param1 = 1 AND table.field2 IS NOT NULL))
...which doesn't make sense if you already have Table.Field = stuff, etc...
...如果你已经有了Table.Field = stuff，这没有意义，等等......
Options that would perform better would be to either make the entire query dynamic SQL, or if there's only one parameter - use an IF/ELSE statement with separate queries & the correct WHERE clauses.
性能更好的选项是使整个查询成为动态 SQL，或者如果只有一个参数 - 使用带有单独查询和正确 WHERE 子句的 IF/ELSE 语句。
