Re: CONTAINS function and OR

Edward wrote on Fri, 20 Jan 2006 09:38:58 +1100:

> 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


.

Relevant Pages

  • 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)
  • CONTAINS function and OR
    ... I am attempting to create a query with a "dynamic" CONTAINS query, ... SELECT * FROM Jobs WHERE (@Keywords IS NULL OR CONTAINS(JobTitle, ... I get the original error. ...
    (microsoft.public.sqlserver.fulltext)
  • Re: Search a database using 3 fields and an input text box.
    ... This is more of a database question than anything because your query ... Your query will have to compare each ... Spliton the textbox value to get an array of keywords. ...
    (microsoft.public.dotnet.framework.aspnet)
Loading