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?
Advertisement
Answer
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'