How to Short-Circuit SQL Where Clause
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.
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.
Why not do a CASE in the WHERE condition?
DECLARE @tester TABLE ( theDate DATE, theValue INT )INSERT INTO @tester VALUES ('2013-10-17', 35)INSERT INTO @tester VALUES ('2013-10-16', 50)INSERT INTO @tester VALUES ('2013-10-15', 2)declare @queryWord as nvarchar(20) = 'asdas'SELECT *FROM @testerWHERE theDate = CASE WHEN ISDATE(@queryWord) = 1 THEN CONVERT(Date, @queryWord) ELSE theDate ENDSET @queryWord = '2013-10-17'SELECT *FROM @testerWHERE theDate = CASE WHEN ISDATE(@queryWord) = 1 THEN CONVERT(Date, @queryWord) ELSE theDate END