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 + '%')
)
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.