The case of SQL and LIKE is where the article

Question

I rack my brains over this hope that is 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 from another related post here .

I get an error message:

Incorrect syntax next to the keyword "LIKE".

Answer 1

 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 @SearchTerm starts with / end with % → or uses '%' + @SearchTerm + '%' .

Additional information about 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 

Answer 2

If you do not need to check the inbetween string, you can do the trick as shown 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 this:

 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)