CODEWITHSUNDEEP
sql-servert-sql
user107242
user107242

Reputation: 59

SQL CASE statement with LIKE (MSG 156)

This part of code is in a WHERE clause:

AND
  C = CASE
    WHEN @Variable = 'NA' THEN C
    ELSE LIKE '%' + @Variable + '%'
  END

Here's what I want: If @Variable is 'NA'(as in not applicable), then give me all results. If not, I want only C like @Variable.

The error is with the like. If I remove the = from C = CASE, there's no more error with LIKE, but the error is with CASE instead. How should I proceed?

Upvotes: 0

Views: 91

Answers (3)

shawnt00
shawnt00

Reputation: 17915

Ultimately what you want is OR. I recommend using that approach, as given by the other answers, however it is indeed possible to fix your syntax problem using CASE.

AND
  'Keep' = CASE
    WHEN @Variable = 'NA' THEN 'Keep'
    WHEN C LIKE '%' + @Variable + '%' THEN 'Keep'
    ELSE 'Discard'
  END

I tried to also highlight that the values being compared don't really matter nor is the ELSE strictly necessary.

Upvotes: 0

Marc B
Marc B

Reputation: 360702

That syntax will never work, and why go for something so complicated?

WHERE (@var = 'ok') OR (c like '%' + @var + '%')

Upvotes: 1

Tyler Roper
Tyler Roper

Reputation: 21672

This can be done in a simple WHERE, like so:

SELECT * FROM MyTable
WHERE (@Variable = 'OK') OR (C LIKE '%' + @Variable + '%')

Upvotes: 2

Related Questions