How to do a conditional statement in SQL where clause

Question

What I'm trying to do is when @aValue parameter is like 'abc%' then run

select 
    f.name, f.amount, f.date 
from 
    fakeTable f
where f.name like @aValue
    and f.date > '01/01/2000'

Otherwise if the @aValue param is not like 'abc%', then select all the records where the f.name is equal to @aValue.

select 
        f.name, f.amount, f.date 
    from 
        fakeTable f
    where f.name = @aValue
        and f.date > '01/01/2000'

I'm trying to accomplish this using a CASE statement within my Where. In my where statement I'm getting an error

Incorrect syntax near LIKE

My query:

Declare @aValue varchar(5) = 'abcde';
-- @aValue = 'xyz';

select 
    f.name, f.amount, f.date 
from 
    fakeTable f
where 
    f.name = case 
                 when @aValue 'abc%' 
                     then f.name like @aValue
                 else f.name = @aValue
             end
    and f.date > '01/01/2000'

What am I doing wrong that is causing the syntax error?

Answer 1

Instead of the CASE expression, use the OR and AND operators like this:

WHERE (
        (@aValue LIKE 'abc%' AND f.name LIKE @aValue) 
        OR 
        (@aValue NOT LIKE 'abc%' AND f.name = @aValue)
      )
  AND f.date > '01/01/2000'