SQL Server Contains Predicate

Question

I'm putting together a quick search that has several fields like category, year, etc. I'm also trying to include a field for search term that uses the contains predicate on the title column. I'd like to use the same query for if they've selected a search term or not so I tried the following, but I always get 0 results if the @SearchTerm is empty.

AND (
@SearchTerm IS NOT NULL AND
CONTAINS((Title), @SearchTerm)
)

Is there an easy way to ignore the searchterm contains section if the searchterm is empty?

Thanks!

Answer 1

I think the logic you want is OR, not AND, phrased like this:

AND (@SearchTerm IS NULL OR CONTAINS((Title), @SearchTerm))