Short circuit WHERE using CASE containing a CONTAINS

Question

I'm attempting to write something like:

SELECT Id FROM SomeTable 
WHERE
 CASE WHEN (@param IS NULL) THEN
  1
 ELSE
  CONTAINS([FullText],@param)
 END = 1

but I can't seem to get SQL Server not to complain about the syntax. Is there a way to use CASE to short-circuit the CONTAINS search?

Even doing something like this doesn't seem to short-circuit:

        CASE WHEN (@param IS NULL) THEN
            1
        ELSE
            (CASE WHEN CONTAINS([FullText], @param ) THEN
                1
            ELSE
                0
            END)
        END = 1

Possible duplicate of [Is the SQL WHERE clause short-circuit evaluated?](http://stackoverflow.com/questions/789231/is-the-sql-where-clause-short-circuit-evaluated) — Mulan, May 18 '16 at 03:20
Using `OR` should be sufficient, as the statements would be evaluated from left to right ( eg `WHERE @param IS NULL OR CONTAINS([FullText], @param )` ) — Anthony, May 18 '16 at 04:11

score 1 · Answer 1 · answered May 18 '16 at 03:33

1

If you look at the execution plan you can see that case is translated to a series of if... else where both part are executed.
It seems like the only way to avoid execution of undesirable part is

if @param is null
   select * from myTable
else
   select * from myTable
     where <expensive condition check>

answered May 18 '16 at 03:33

Alex Kudryashev

9,120
3
27
36

Unfortunately I can't use `IF` in my particular scenario. – Mr. Smith May 18 '16 at 03:37

score 1 · Answer 2 · answered May 18 '16 at 04:02

1

Just simplify your query :

SELECT Id FROM SomeTable 
WHERE @param IS NULL OR CONTAINS([FullText],@param)

So if @param is NULL it will not check for second condition (short circuit)

answered May 18 '16 at 04:02

Anonymous Duck

2,942
1
12
35

SQL Server does not guarantee short-circuit behavior; the CONTAINS in your query will throw a null predicate error. – Mr. Smith May 18 '16 at 05:21
@Mr.Smith can you provide a sql fiddle? Take a look in this post on how to handle the predicate error http://stackoverflow.com/questions/9216608/is-full-text-search-contains-clause-always-evaluated. The query is fine just add some minor validations for the `contains` function – Anonymous Duck May 18 '16 at 05:24

score 0 · Answer 3 · answered May 18 '16 at 04:35

Since sql server doesn't guarantee short-circuit conditions, you can do something like this to avoid the null predicate error:

SELECT Id 
FROM SomeTable 
WHERE @param IS NULL 
OR CONTAINS([FullText], ISNULL(@param, 'a'))

This way, if @param is null, you will not get an error. I'm not so sure about performance, however - if there is no short-circuit conditions it means that perhaps both parts of the where clause will evaluate and that might take a while.

Short circuit WHERE using CASE containing a CONTAINS

3 Answers3