Search code examples
sqlsql-serversql-likecontainstable

SQL Server Contains Predicate

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!

Solution

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

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