CASE Statement in Where clause using Like Operator

  • A+
Category:Languages

Good Day

I am facing an issue using CASE expression in where clause, To my knowledge syntax is correct but getting a error, Below is my code:

Where CASE              WHEN (@ItemFor='' and @ItemTo='')             THEN id like '%'             ELSE id between @ItemFor and @ItemTo         END 

Above code looks correct to me but I am getting syntax error saying Incorrect syntax near the keyword 'like'.

Please help.

EDIT:

I have 4 sets of parameters and all need to go to where clause and all are string values:

WHERE          CASE              WHEN (@ItemFor='' and @ItemTo='')                 THEN id like '%'                 ELSE id between @ItemFor and @ItemTo         END     AND          CASE              WHEN (@CodeFrom='' and @CodeTo='')             THEN Code like '%'             ELSE code between @CodeFrom and @CodeTo         END 

 


You could simplify and use this condition:

WHERE ((@ItemFor='' and @ItemTo='' AND id like '%') OR id between @ItemFor and @ItemTo)   AND ((@CodeFrom='' and @CodeTo='' AND Code like '%') OR code between @CodeFrom and @CodeTo) 

and now syntax is correct.

Moreover, id like '%' and Code like '%' make no sense, because they are always true. They can be read as "check if id (or Code) is anything", so they can (should) be removed.

Comment

:?: :razz: :sad: :evil: :!: :smile: :oops: :grin: :eek: :shock: :???: :cool: :lol: :mad: :twisted: :roll: :wink: :idea: :arrow: :neutral: :cry: :mrgreen: