7645 Null or empty full-text predicate

Question

I have a query that ran fine on SQL2005 but moving the database to SQL2008 gives me the error from the title.

The code that is the problem is a call to CONTAINS, CONTAINSTABLE or FREETEXT with an empty parameter. However I'm trying to only call or join when there is a value like such

where (@search_term = '' or (FREETEXT(lst.search_text, @search_term)))

or

left join containstable (listing_search_text, search_text,  @search_term) ftb on l.listing_id = ftb.[key] 
    and len(@search_term) > 0

However I cannot find any workaround for this to work on SQL2008. Any ideas?

I know I can do dynamic SQL or have a if statement with two different cases (select with FT join, select without FT join. Any better workaround which doesn't require doing this?

Ebracteate

Answer 1

I found the answer to this today when converting my own database from SQL 2005 to SQL 2008.

Pass "" for your search term and change the @search_term = '' test to be @search_term = '""' SQL server will ignore the double quotes and not throw an error.

For example, the following would actually returns all records in the Users table:

declare  @SearchTerm nvarchar(250)

SET @SearchTerm = '""'

select UserId, U.Description, U.UserName
from dbo.Users U
WHERE ((@SearchTerm = '""') OR CONTAINS( (U.Description, U.UserName), @SearchTerm))

If you are using .Net, you might grab a copy of E. W. Bachtal's FullTextSearch class. His site is very informative: http://ewbi.blogs.com/develops/

Varrian

(@SearchTerm = '""') predicate adds an extreme amount of reads and the query time outs occasionally (as noted by whiplashtony) – Hsu

Answer 2

This solution didn't work for me on SQL 2008. The answer seemed pretty clear and was deemed useful but I would get time outs on a table with 2M records. In fact it locked up a server just running the query in SSMS.

It didn't seem to like the OR in the where clause but I could run the query fine separating the conditions.

I ended up using a UNION successfully as a workaround.

declare  @SearchTerm nvarchar(250)

SET @SearchTerm = '""'

select UserId, U.Description, U.UserName
from dbo.Users U
WHERE ((@SearchTerm = '""') 

UNION 

select UserId, U.Description, U.UserName
from dbo.Users U
WHERE CONTAINS( (U.Description, U.UserName), @SearchTerm)) 

Statvolt

The problem you had probably is due to the fact that the one I have is by no means optimized. Lately I've been finding that using UNIONs in place of OR's in WHERE clauses runs several times faster. So +1 – Varrian

I had a query with tons of LIKEs and leading wildcards that was running like a dog. I'd tried Full Text indexing which didn't help at all. Using UNIONs instead of ORs got my query from ~15 seconds to less than 1. +1!!!! – Precipitant

what if I had an inner join between two tables and I wanted to do a full text search on the two tables? that means I will need about 3-4 unions? – Eimile

Answer 3

Just ADD Double quotes. You can check for empty string and then add double quotes in it.

Set @search_term = case when @search_term = '' then '""' else @Address End

Here you go -

where (@search_term = '""' or (FREETEXT(lst.search_text, @search_term)))

Solar

Answer 4

The problem with FTS and the OR operand was fixed in SP2 CU4. The OR condition should run ok without having to UNION if you are on that level or later. We tried a very recent update of SP2 CU8 and FTS works with OR now. Also, searches such as 3.12 which would fail before now work just fine.

Resound

Answer 5

I found that using "a" as the default works if SQL-Server is configured to ignore "noise words".

SET @SearchPhrase = coalesce(@SearchPhrase, 'a'); /* replace with 'a' if null parameter */ 
SELECT ... WHERE 
    (@SearchPhrase = 'a' OR contains(Search_Text, @SearchPhrase)) 

Siracusa