39

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?

mjallday
  • 9,796
  • 9
  • 51
  • 71

5 Answers5

58

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/

dhke
  • 15,008
  • 2
  • 39
  • 56
NotMe
  • 87,343
  • 27
  • 171
  • 245
  • 3
    (@SearchTerm = '""') predicate adds an extreme amount of reads and the query time outs occasionally (as noted by whiplashtony) – Jakub Linhart May 11 '12 at 07:51
14

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))
whiplashtony
  • 216
  • 2
  • 5
  • 3
    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 – NotMe Apr 18 '11 at 19:25
  • 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!!!! – Melanie Dec 07 '12 at 17:33
  • 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? – Ala' Alnajjar Dec 31 '12 at 09:30
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)))
Abhishek Chandel
  • 134
  • 1
  • 4
2

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.

beachboy301
  • 21
  • 3
1

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))
FloverOwe
  • 302
  • 2
  • 8