如何短路 SQL Where 子句

本文介绍了如何短路 SQL Where 子句的处理方法，对大家解决问题具有一定的参考价值，需要的朋友们下面随着小编来一起学习吧！ 问题描述

我正在尝试在 SQL 服务器中执行以下查询:

I am trying to perform the following query in SQL server:

declare @queryWord as nvarchar(20) = 'asdas' SELECT * FROM TABLE_1 WHERE (ISDATE(@queryWord) = 1) AND TABLE_1.INIT_DATE = CONVERT(Date, @queryWord)

这显然会导致错误，因为 'asdas' 无法转换为 Date.虽然，我期待不同的行为.也就是说，因为 ISDATE(@queryWord) = 1 是假的，我希望 SQL 不检查第二个条件，但显然，它确实如此.

This obviously causes an error because 'asdas' cannot be converted to Date. Although, I was expecting a different behavior. That is, because ISDATE(@queryWord) = 1 is false, I was expecting SQL to do not check the second condition, but apparently, it does.

我知道还有其他一些方法可以执行此查询，但这不是我的问题.不知道有没有什么办法不检查第二个条件是不是第一个不满足.我很好奇，因为我认为 SQL 已经做到了.

I know there are some other ways to perform this query but this is not my question. I wonder if there is some way to do not check the second condition is the first one does not satisfy. I am curious because I thought that SQL already did this.

推荐答案

SQL Server 不执行短路(也不应该).

SQL Server does not do short-circuiting (nor should it).

如果您需要它在某些情况下不尝试某事，您需要以编写查询的方式强制这样做.

If you need it to not try something under some circumstances, you need to force that in the way that you write your query.

对于此查询，最简单的解决方法是在 WHERE 子句中使用 CASE 表达式.

For this query the easiest fix would be to use a CASE expression in your WHERE clause.

declare @queryWord as nvarchar(20) = 'asdas' SELECT * FROM TABLE_1 WHERE TABLE_1.INIT_DATE = (CASE WHEN ISDATE(@queryWord) = 1 THEN CONVERT(Date, @queryWord) ELSE NULL END)

副手，CASE 和查询嵌套是我能想到的唯一两种受支持的方法，可以强制对 SQL 中的依赖条件进行求值顺序.

Off-hand, CASE and query-nesting are the only two supported ways that I can think of to force an order of evaluation for dependent conditions in SQL.