How to Short-Circuit SQL Where Clause

Question

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)

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 not check the second condition, but apparently, it does.

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.

Answer 1

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.

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)

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.

Answer 2

I Guess you could do it in 2 passes:

declare @queryWord as nvarchar(20) = 'asdas'


    select
    *
    from
    (
    SELECT  * FROM TABLE_1 
    WHERE (ISDATE(@queryWord) = 1) ) t1
    where t1.INIT_DATE = CONVERT(Date, @queryWord)

So your inner query runs the first test and the outer query the second. In a single query, I don't believe there is any way to force any order of evaluating conditions.