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?

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/

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))