title: Case in Where clause?
body: <p>I have to write one Stored Procedure, In which I have to find the value from the table on the basis of Last Name, Last name search criteria can be Exact, Begin with or Phonetic. 
  SP will be like - </p>
  
  <pre><code>SELECT * FROM  Person       
      WHERE (
         CASE @lastNameCriteria 
         WHEN 'EXACT' THEN    Person.LastName = @LastName
         WHEN 'BEGIN' THEN    Person.LastName like @LastName
         ELSE SOUNDEX(tblPerson.LastName) LIKE SOUNDEX(@lastName))                     
  </code></pre>
  
  <p>I dont know above query will give in SQL. I dont want to use dynamic query, I can not use "IF ELSE", because there might be some other criteria like FirstNameCriteria, MiddleNameCriteria, which will increase the complexity of "IF ELSE".
  Please suggest me what to do.</p>