SQL OR statement hit when previous condition is true - Full Text Search

Question

In the query below I would expect the OR CONTAINS(d.Data, @SearchText) never to be reached due to @SearchText being set to NULL. However running this query throws a "Null or empty full-text predicate." error because the CONTAINS(d.Data, @SearchText) is called with a null value for @SearchText.

Any ideas why the second part of the OR statement is called when the first part is true?

DECLARE @searchText nvarchar(300);
set @SearchText = null

SELECT * FROM Person p      
        JOIN PersonDocument pd ON p.Id = pd.PersonId
        JOIN Document d ON d.Id = pd.DocumentId
    WHERE @SearchText IS NULL OR CONTAINS(d.Data, @SearchText)

See this answer: http://stackoverflow.com/questions/789231/is-the-sql-where-clause-short-circuit-evaluated — Oliver, Sep 05 '14 at 09:17

score 0 · Answer 1 · edited Sep 05 '14 at 09:18

0

Contains will get called because it's an OR statement, even if it the value wasn't null it would still get called due to the OR statement, I think you need a guard statement such as:

WHERE @SearchText IS NULL OR @SearchText IS NOT NULL AND CONTAINS(d.Data, @SearchText)

edited Sep 05 '14 at 09:18

Oliver

8,794
2
40
60

answered Sep 05 '14 at 09:08

KryptonicSun

31
1

Thanks for the suggestion - however I'm getting the same problem with the additional guard. – robasaurus Sep 05 '14 at 09:18

score 0 · Accepted Answer · answered Sep 05 '14 at 09:37

I got it working in the end like this:

DECLARE @searchText nvarchar(300);
set @SearchText = null -- imagine this parameter was passed in null
IF @SearchText is null
BEGIN
    SET @SearchText = '""'
END 

SELECT * FROM Person p      
        JOIN PersonDocument pd ON p.Id = pd.PersonId
        JOIN Document d ON d.Id = pd.DocumentId
    WHERE @SearchText = '""' OR CONTAINS(d.Data, @SearchText)

SQL OR statement hit when previous condition is true - Full Text Search

2 Answers2