How to do a conditional statement in SQL where clause-CodePudding

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?

CodePudding user response：

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'

CodePudding user response：

Instead of trying to juggle conditions in a CASE Expression you can do this with boolean logic:

WHERE 
   (
      f.name = @aValue
      OR
      (
          @aValue LIKE 'abc%'
          AND
          f.name LIKE 'abc%'
      )
   )

CodePudding user response：

I would suggest using union and a query for each case, this is often better than trying to combine multiple criteria which can prevent efficent index usage.

Something along the lines of the following, the first test on @value being true should mean only one or other of the queries actually executes.

select <columns>
from table
where @value like 'abc%' and name like @value '%'

union all

select <columns>
from table
where @value not like 'abc%' and name = @value