7645 Null or empty full-text predicate

abigblackman_6084 0
Viewed 30531

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?

full-text-search sql-server
5 Answers

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/

Chris_Lively_24240

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