Case in Where clause?

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 -

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))                     

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.

Don't do it in a sinlge SELECT, you will kill any chance for the optimizer to come up with a reasonable execution plan. Use three distinct SELECTs instead:

IF @lastNameCriteria  = 'EXACT' THEN
   SELECT * FROM  Person WHERE Person.LastName = @LastName
ELSE IF @lastNameCriteria   = 'BEGIN' THEN
  SELECT * FROM  Person WHERE Person.LastName like @LastName
ELEE
  SELECT * FROM  Person WHERE SOUNDEX(tblPerson.LastName) LIKE SOUNDEX(@lastName)

This way the query optimizer can create the appropriate plan for each case and, at the very least, use an index for the EXACT case.

You can use the CASE statement properly like this

SELECT *
FROM Person 
WHERE
    CASE @lastNameCriteria
    WHEN 'EXACT' THEN
        CASE WHEN Person.LastName = @LastName THEN 1 END
    WHEN 'BEGIN' THEN
        CASE WHEN Person.LastName like @LastName THEN 1 END
    ELSE
        CASE WHEN SOUNDEX(tblPerson.LastName) = SOUNDEX(@lastName) THEN 1 END
    END = 1

Which will force SQL Server to evaluate the @lastNameCriteria before processing the embedded conditions. You can also use multiple OR clauses which by virtue of @lastNameCriteria being compared to a string literal will cause short-circuit boolean evaluation - the parts involving the column comparison is not evaluated unless the @lastNameCriteria matches).

SELECT *
FROM Person 
WHERE
(@lastNameCriteria = 'EXACT' AND Person.LastName = @LastName)
OR
(@lastNameCriteria = 'BEGIN' AND Person.LastName like @LastName)
OR
(isnull(@lastNameCriteria,'') NOT IN ('EXACT','BEGIN')
 AND SOUNDEX(tblPerson.LastName) = SOUNDEX(@lastName))
--option(recompile)

Notes:

1. Don't (have to) use LIKE between the SOUNDEX, it is a simple = that you are after
2. I made no assumptions about @lastNameCriteria which is why it is tested for both nulls and not in the other two
3. If you are worried about parameter sniffing or incorrect query plans, uncomment the option (recompile) bit. Planning (each time) for such a simple query should take insignificant time.

To answer the OP's question, you could do it like this:

SELECT * FROM  Person       
WHERE 
   (@lastNameCriteria = 'EXACT' and Person.LastName = @LastName) or
   (@lastNameCriteria = 'BEGIN' and Person.LastName like @LastName) or
   (@lastNameCriteria = 'SOUNDEX' and SOUNDEX(tblPerson.LastName) LIKE SOUNDEX(@lastName))