MS-SQL 2005 search: conditional where clause with freetext

Question

I'm writing a fairly complex stored procedure to search an image library.

I was going to use a view and write dynamic sql to query the view, but I need to use a full text index, and my view needs outer joins (http://stackoverflow.com/questions/1094695/ms-sql-2005-full-text-index-on-a-view-with-outer-joins)

So, I'm back to a stored procedure.

I need to search on (all optional):

• a general search query that uses the full text index (or no search terms)
• one or more categories (or none)
• a single tag (or none)

Is there a way to do a conditional FREETEXT in the 'WHERE' clause? The query may be empty, in which case I want to ignore this, or just return all FTI matches.

...AND FREETEXT(dbo.MediaLibraryCultures.*, '"* "') doesn't seem to work. Not sure how a case statement would work here.

Am I better off inserting the category/tag filter results into a temp table/table variable, then joining the FTI search results? That way I can only do the join if the search term is supplied.

Thoughts?

Answer 1

You could add a check for the empty search string like

where ...
AND (FREETEXT(dbo.MediaLibraryCultures.*, @FreeTextSearchFor) OR @FreeTextSearchFor = '')

(I have a feeling that freetext searches can't have null passed into them, so I'm comparing to an empty string)

If the term to search for is empty, the whole clause will evaluate to true, so no restrictions will be applied (by this clause) to the rows returned, and of course since its a constant being compared to a variable - I would think the optimizer would come into play and not perform that comparison for each row.

Answer 2

Hmm, I thought there was no short-circuiting in sql server?

AND (@q = '' OR FREETEXT(dbo.MediaLibraryCultures.*, @q))

seems to work just fine!

Strangely, the full text scan is still part of the execution plan.

Answer 3

I know it's a year later and a newer version of SQL but FYI...

I am using SQL Server 2008 and have tried to short circuit using

AND ( @searchText = '' OR freetext(Name, @searchText))

and I receive the message "Null or empty full-text predicate" when setting @searchText = ''. I guess something in 2008 has changed that keeps short circuiting from working in this case.