Search code examples
sqlcasewhere-clausesql-like

Using LIKE predicate inside CASE expression in WHERE clause

I have a table of names:

John
Peter
Peter-Paul
Mary

I have a drop down list of these names and I have added 'All' as an option at the top.

I want to pass the name selected from the drop down list as a parameter to a where clause.

What I am trying to do is only have the where clause if name is not passed as 'All'

SELECT City, State
FROM EmployeeDetails
WHERE @NAME =
              CASE @NAME
                         WHEN 'All' THEN '1=1'
              ELSE (EmployeeDetails.NAME LIKE '' + @NAME + '%')

I can't get this to work. The ELSE statement gives the error 'Incorrect syntax near the word 'LIKE'.

Looking at other StackOverflow questions I have found:

  1. CASE statements return scalar values so it makes sense that it doesn't like my LIKE statement.
  2. Putting the ELSE clause in single quotes returns no results (breaks where clause)
  3. Trying to do an IF(@NAME = 'All') breaks the where clause and in every question on StackOverflow the consensus is to turn IF statements into CASE statements.

When running the WHERE clause not in a CASE statement, it works for a specific name such as:

WHERE (EmployeeDetails.NAME LIKE '' + @NAME + '%')

If I search for Peter it would return 2 results as expected. Mary 1 result, etc.
I obviously can't pass 'All' to that.

Solution

  • Don't use case in the where. Simply do:

    SELECT City, State
FROM EmployeeDetails
WHERE (@NAME = 'All') OR (EmployeeDetails.NAME LIKE '' + @NAME + '%');

    I'm not sure what the purpose of '' + is, but you have it in your question, so I'm leaving it.