CONTAINS function and OR

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?


.

Relevant Pages

  • Re: CONTAINS function and OR
    ... > I am attempting to create a query with a "dynamic" CONTAINS query, ... > DECLARE @Keywords VARCHAR ... > SELECT * FROM Jobs WHERE (@Keywords IS NULL OR CONTAINS(JobTitle, ... Depending on the order it processes clauses, and what they contain, it might ...
    (microsoft.public.sqlserver.fulltext)
  • php/mySQL search/mysql_num_rows() error
    ... Couldn't execute query ... //separate key-phrases into keywords ... // check for an empty string and display a message. ... foreach { ...
    (alt.php)
  • RE: Merge multiple detail records in a single control
    ... "toehrtman" wrote: ... Duane's examples and structured an SQL statement in the query to concatenate ... keywords as separate lines in the detail section. ...
    (microsoft.public.access.reports)
  • RE: Merge multiple detail records in a single control
    ... Thanks for pointing me in the right direction, Duane Hookom's Concatenate ... Duane's examples and structured an SQL statement in the query to concatenate ... keywords as separate lines in the detail section. ...
    (microsoft.public.access.reports)
  • Re: File manipulation
    ... > I have one text files one for Oracle query ... > of Ocacle query to its related keywords in SQL Server query and create a ... > new file that I can use to create my SQL Server query. ...
    (microsoft.public.vb.general.discussion)
Loading