title: SQL CASE in Where Clause?
body: <p>I have a two optional parameters empName, empLoc. Both of these parameters can be null, empty or have some value. If both has values, i will have to get all the values, if empName has value then i only need to get value where empName is equal to passed parameter @empName, and same with @empLoc. I know i can write separate sql's in same stored procedure but i want to do it using CASE clause, since it is going to save most of the repeated code. However i am having problem while setting up query. And i know it's the CASE clause.
  Here is what my query looks like:</p>
  
  <pre><code>SELECT 
       EmpID,
       EmpDept
  FROM Employee
  WHERE TimeStamp = @timeStamp
   **AND (CASE
          WHEN DATALENGTH(@empName) &gt; 0 THEN EmployeeName LIKE '%' +@empName+'%'
        ELSE
           EmployeeLocation LIKE '%' +@empLoc+'%'
        END)**
  </code></pre>
  
  <p>NOTE: im already doing null check for these parameters. Also this query will only run if either @empName or @empLoc has some value.</p>
  
  <p>Any suggestions how to fix this?</p>