4

I have been breaking my head over this hope it's possible

declare @locationType varchar(50);
declare @SearchTerm NVARCHAR(100);

SELECT column1, column2
FROM whatever
WHERE
CASE @locationType
    WHEN 'location' THEN account_location LIKE @SearchTerm
    WHEN 'area' THEN Area LIKE @SearchTerm
    WHEN 'division' THEN xxx_location_division LIKE @SearchTerm
END

I copied the code form another related post here.

I get the error:

Incorrect syntax near the keyword 'LIKE'.

pirho
  • 11,565
  • 12
  • 43
  • 70
Deeptechtons
  • 10,945
  • 27
  • 96
  • 178

2 Answers2

6
declare @locationType varchar(50);
declare @SearchTerm NVARCHAR(100);

SELECT column1, column2
FROM whatever
WHERE
   (@locationType = 'location' AND account_location LIKE @SearchTerm)
OR
   (@locationType = 'area' AND Area LIKE @SearchTerm)
OR
   (@locationType = 'division' AND xxx_location_division LIKE @SearchTerm)

Make sure that @SearchTerm starts with/end with % -> or use '%' + @SearchTerm + '%'.

More info on LIKE operator.

--- Update ----

SELECT column1, column2
FROM whatever
WHERE
(
  CASE @locationType
     WHEN 'location' THEN account_location
     WHEN 'area' THEN Area
     WHEN 'division' THEN xxx_location_division
  END
) LIKE @SearchTerm
Francois
  • 10,730
  • 7
  • 47
  • 80
  • so `LIKE`, `WHERE` AND `CASE` don't go together? also you meant starts with `/` and ends with `%` right? – Deeptechtons Nov 28 '11 at 11:12
  • yep, my mistake for ==. If you look for places stating with "NEW", then `@SearchTerm` should be "NEW%". – Francois Nov 28 '11 at 11:18
  • @FrancoisB. Shocking doesn't not pick the valid area location records using this query. – Deeptechtons Nov 28 '11 at 11:28
  • `CASE` cannot be used to return a search criteria (for instance `account_location LIKE @SearchTerm`). This how I guess the DB analyse/ understand you query. See my updated answer. – Francois Nov 28 '11 at 11:44
0

If you not need to check inbetween string then you can do trick like below :

SELECT column1, column2 
FROM whatever 
WHERE @SearchTerm LIKE 
CASE @locationType 
    WHEN 'location' THEN account_location
    WHEN 'area' THEN Area 
    WHEN 'division' THEN xxx_location_division 
END

Or you can do like :

SELECT column1, column2 
FROM whatever 
WHERE 
   (@locationType = 'location' AND account_location LIKE @SearchTerm) 
OR 
   (@locationType = 'area' AND Area LIKE @SearchTerm) 
OR 
   (@locationType = 'division' AND xxx_location_division LIKE @SearchTerm)
Upendra Chaudhari
  • 6,473
  • 5
  • 25
  • 42