SQL Server "Optional" FreeText Search

Question

I'm writing a search query in SQL Server 2005 for a discussion board. The proc takes several parameters, but most are "optional". There's one search field for Message Body, which I have a full text index on. Here's the problem..

If I pass in a value to search against with FreeText, the search works fine (thank you Microsoft). However, the message body field is optional, meaning that in my query, I want to handle a "search all". How can I default my query to just use any\all records regardless of the data held in my message body field?

I know this doesn't work, but if no value is returned for the message body parameter, Im looking for something like:

where (FREETEXT(msg.messagebody, '*'))

Answer 1

You could do something like:

select * from Products_CatalogProducts where (@keywords='*' or freetext(msg.messagebody,@keywords))

Assuming you passed in @keywords with a * if it's blank