sql server full text search:
Edward wrote on Fri, 20 Jan 2006 09:38:58 +1100:
[quoted text, click to view] > I am attempting to create a query with a "dynamic" CONTAINS query, e.g.
>
> DECLARE @Keywords VARCHAR(128)
> SET @Keywords = NULL
>
> SELECT * FROM Jobs WHERE (@Keywords IS NULL OR CONTAINS(JobTitle,
> @Keywords))
>
> However, the CONTAINS function seems to behave very weirdly when OR is
> involved. Even though @Keywords is null, it still evaluates the contains,
> and fails because of the null.
>
> Server: Msg 7603, Level 15, State 1, Line 40
> Syntax error in search condition, or empty or null search condition
> ''.
>
> On the other and, if I change the query to this:
>
> SELECT * FROM Jobs WHERE (@Keywords IS NULL OR CONTAINS(JobTitle,
> @Keywords)) AND JobID IN (SELECT JobID FROM JobSkills WHERE SkillID = 2)
>
> It works fine.
>
> But that's not it. If I move the order of the clauses:
>
> SELECT * FROM Jobs WHERE JobID IN (SELECT JobID FROM JobSkills WHERE
> SkillID = 2) AND (@Keywords IS NULL OR CONTAINS(JobTitle, @Keywords))
>
> I get the original error. Same goes for adding another contains clause at
> the beginning. It seems that the original CONTAINS works fine, others defy
> all logic.
>
> Can someone suggest what is causing this behaviour (or why it works like
> this - doesn't seem to make any sense) and a possible workaround short of
> using hideous dynamic SQL?
It depends on the query parser and how it decides to process the query.
Depending on the order it processes clauses, and what they contain, it might
skip the CONTAINS clause completely (which it appears to do in the 2nd
case). Using FTS clauses when unnecessary will impact performance as the FTS
process is external to SQL Server. You could try doing the following:
IF COALESCE(@Keywords,'') = ''
SELECT * FROM Jobs
ELSE
SELECT * FROM Jobs WHERE CONTAINS(JobTitle, @Keywords)
END
This avoids dynamic SQL, and prevents the error is @Keywords is NULL or
empty (an empty string will also cause an error, not just a NULL)
Dan
