CASE Statement in Where clause using Like Operator

Question

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'.

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

Possible duplicate of ["CASE" statement within "WHERE" clause in SQL Server 2008](https://stackoverflow.com/questions/8785209/case-statement-within-where-clause-in-sql-server-2008) — SMor, Jul 27 '18 at 11:22

Gordon Linoff · Answer 1 · 2018-07-27T11:10:20.470

5

You don't need a case expression for this. Just do:

where ((@ItemFor = '' and @ItemTo = '') or
       id between @ItemFor and @ItemTo
      )

Your version doesn't work because SQL Server doesn't have boolean variables. Your a case expression is trying to return a boolean expression, but such an expression is not a value.

By the way, you probably intend:

where (id >= @itemfrom or @itemfrom = '') and
      (id <= @itemto or @itemto = '')

This allows you to set only one of the limits.

edited Jul 27 '18 at 11:10

answered Jul 27 '18 at 11:08

Gordon Linoff

1,242,037
58
646
786

Thanks sir, but I have more conditions in where clause after this, need to add with `AND`... – Siva Jul 27 '18 at 11:09
can you please help me with a way out my requirement is if user enter some value in parameter then need to filter those else take all values from database.. like this I have 4 parameters – Siva Jul 27 '18 at 11:10
@Siva . . . Add more conditions. This is just another condition to be checked. – Gordon Linoff Jul 27 '18 at 11:10
1

@Siva . . . Modifying the question after it has been answered can be rude. You already had answers to your original question. The modifications tend to invalidate those answers. – Gordon Linoff Jul 27 '18 at 11:49
No Sir, I have edited before it has been answered.. its been edited after that – Siva Jul 27 '18 at 11:54
Thanks for Solution and Explanation sir.. it really helped me, but one more expert just extended your solution. anyway thanks sir – Siva Jul 27 '18 at 12:26

score 3 · Accepted Answer · answered Jul 27 '18 at 11:22

3

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.

answered Jul 27 '18 at 11:22

Michał Turczyn

32,028
14
47
69

Turczyn yes sir.. but I have a requirment with 4 parameters and user may enter all or not enter value for single parameter or may enter 1 value and 3 blank.. so its dynamic, so trying to get some solution out of it – Siva Jul 27 '18 at 11:25
@Siva So try the code I gave - it should work exactly the way you want :) – Michał Turczyn Jul 27 '18 at 11:26
@Siva If you solved your problem using my answer, you should accept it :) – Michał Turczyn Jul 27 '18 at 11:44
Sure sir.. just trying will accept once result comes – Siva Jul 27 '18 at 11:45

score 2 · Answer 3 · answered Jul 27 '18 at 11:22

This may solve your problem:

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

score 1 · Answer 4 · answered Jul 27 '18 at 11:14

1

Since CASE cannot return a boolean value, you'll have to return something else and compare:

WHERE
  CASE
    WHEN Condition1 THEN 1
    WHEN Condition2 THEN 0
    WHEN Condition3 THEN 1
    ELSE 0
  END = 1

answered Jul 27 '18 at 11:14

Amy B

108,202
21
135
185

thanks for your answer.. but in my case how should I return the value? – Siva Jul 27 '18 at 11:15

score 1 · Answer 5 · answered Jul 27 '18 at 11:17

Try this approach instead.

where (@itemFor='' and @itemto='') OR (id between @itemFor and @itemto)

Doing the same as your case case statement. If both variables are empty, then don't filter the ID clause. If the variables are populated, check the ID within range

Note that if you are doing multiple conditions, I would suggest adding extra parenthesis, such as

where ( (@itemFor='' and @itemto='') OR (id between @itemFor and @itemto) )
   and (firstName like '%JOE%')

etc

LukStorms · Answer 6 · 2018-07-27T12:23:28.690

1

To explain what's wrong with the CASE WHEN.

A CASE WHEN returns a value, which you can choose depending on different criteria.
And it returns a value based on the first criteria that matches.
Hence, it's not supposed to return a criteria.

The example below makes use of that.

WHERE
   (CASE 
    WHEN @ItemFor='' AND @ItemTo='' AND id IS NOT NULL THEN 1
    WHEN id BETWEEN @ItemFor AND @ItemTo THEN 2
    ELSE 0
    END) > 0
   AND
   (CASE 
    WHEN @CodeFor='' AND @CodeTo='' AND Code IS NOT NULL THEN 1
    WHEN Code BETWEEN @CodeFor AND @CodeTo THEN 2
    ELSE 0
    END) > 0

That said, regarding this example.
A solution that doesn't use a CASE WHEN in the WHERE clause, is probably the better approach.

Example:

WHERE id IS NOT NULL
  AND Code IS NOT NULL
  AND ((@ItemFor='' AND @ItemTo='') OR (id BETWEEN @ItemFor AND @ItemTo))
  AND ((@CodeFor='' AND @CodeTo='') OR (Code BETWEEN @CodeFor AND @CodeTo))

Because it's harder for the query optimizer to choose the fastest execution plan based on a CASE WHEN.

edited Jul 27 '18 at 12:23

answered Jul 27 '18 at 11:20

LukStorms

28,916
5
31
45

thanks for answer, but for Gordon solution how to add multiple conditions? can you please help me with that? – Siva Jul 27 '18 at 11:21
@Siva You could add more `WHEN ... THEN ...` to the CASE. But remember, They are evaluated in order. F.e. `case when 2=1 then 1 when 2=2 then 2 when 3=3 then 3 end` returns 2. – LukStorms Jul 27 '18 at 11:29
Thanks for solution and explanation sir – Siva Jul 27 '18 at 12:25
@Siva Btw, I updated my answer. I think just checking if the Id and Code are `NOT NULL` has the same effect as the `LIKE '%'`. Because a `LIKE '%'` is also true on empty strings `col=''`. – LukStorms Jul 27 '18 at 12:27

akshay · Answer 7 · 2018-07-27T11:37:45.633

0

Try this:

WHERE

CASE 
    WHEN (@ItemFor='' AND @ItemTo='') THEN 1
    WHEN (id between @ItemFor and @ItemTo) THEN 1
    WHEN (@CodeFrom='' and @CodeTo='') THEN 1
    WHEN (code between @CodeFrom and @CodeTo) THEN 1
    ELSE 0
END = 1

edited Jul 27 '18 at 11:37

answered Jul 27 '18 at 11:18

akshay

777
4
15

Thanks for solution – Siva Jul 27 '18 at 12:25

Zaynul Abadin Tuhin · Answer 8 · 2018-07-27T11:26:34.943

i guess your mistake area

WHERE

    CASE 
        WHEN (@ItemFor='' and @ItemTo='')
            THEN id like '%'                  -------conditional operator in result
            ELSE id between @ItemFor and @ItemTo ---------- conditional operator
    END
AND 
    CASE 
        WHEN (@CodeFrom='' and @CodeTo='')
        THEN Code like '%'
        ELSE code between @CodeFrom and @CodeTo  -------------conditional operator
    END

you can try below way

WHERE    
case when (@ItemFor='' and @ItemTo='') and id like '%' 
Then (case when id >=@ItemFor and id<=@ItemTo then id else end)
Else 
case when (@CodeFrom='' and @CodeTo='') and Code like '%' 
Then (case when code >=@ItemFor and code<=@ItemTo then code else end)
end

Thanks for Solution and Explanation sir – Siva Jul 27 '18 at 12:25 — Siva, Jul 27 '18 at 12:25

CASE Statement in Where clause using Like Operator

Edit

8 Answers8