Sql where clause, Get All if value is empty or null else use like statement

Question

I have a column

-Category

Red Blue Yellow

I would like to render a conditional statement. If Title is null, then get all, else title like @Title.

  SELECT [Category]
  FROM [dbo].[Records]
  WHERE 
         CASE WHEN @SearchText IS NULL 
                        THEN title = title 
                        ELSE (Title like '%' + @SearchText + '%') 
         END 

The results should look like this

if @Title is null or empty

return all values

Red Blue Yellow

Else where title like '%red%' return red

This is the best I could ome up with

    and (
            (@SearchText IS NULL and title = title)
          or
          (@SearchText IS not NULL and Title like '%' + @SearchText + '%')
        )

Answer 1

Your code can be updated to this:

SELECT [Category]
FROM [dbo].[Records]
WHERE (@SearchText IS NULL OR (Title like '%' + ISNULL( @SearchText ,'')+ '%'))

If you feed null then first condition will be true else second.