"CASE" statement within "WHERE" clause in SQL Server 2008

First off, the CASE statement must be part of the expression, not the expression itself.

In other words, you can have:

WHERE co.DTEntered = CASE                           WHEN LEN('blah') = 0                                THEN co.DTEntered                           ELSE '2011-01-01'                      END 

But it won't work the way you have written them eg:

WHERE     CASE LEN('TestPerson')        WHEN 0 THEN co.personentered  = co.personentered   ELSE co.personentered LIKE '%TestPerson'    END 

You may have better luck using combined OR statements like this:

WHERE (        (LEN('TestPerson') = 0              AND co.personentered = co.personentered        )         OR         (LEN('TestPerson') <> 0              AND co.personentered LIKE '%TestPerson')      )

Although, either way I'm not sure how great of a query plan you'll get. These types of shenanigans in a WHERE clause will often prevent the query optimizer from utilizing indexes.

This should solve your problem for the time being but I must remind you it isn't a good approach :

WHERE             CASE LEN('TestPerson')                WHEN 0 THEN                         CASE WHEN co.personentered  = co.personentered THEN 1 ELSE 0 END                ELSE                         CASE WHEN co.personentered LIKE '%TestPerson' THEN 1 ELSE 0 END            END = 1        AND cc.ccnum = CASE LEN('TestFFNum')                            WHEN 0 THEN cc.ccnum                             ELSE 'TestFFNum'                        END         AND CASE LEN('2011-01-09 11:56:29.327')                 WHEN 0 THEN CASE WHEN co.DTEntered = co.DTEntered THEN 1 ELSE 0 END                 ELSE                     CASE LEN('2012-01-09 11:56:29.327')                         WHEN 0 THEN                             CASE WHEN co.DTEntered >= '2011-01-09 11:56:29.327' THEN 1 ELSE 0 END                         ELSE                             CASE WHEN co.DTEntered BETWEEN '2011-01-09 11:56:29.327'                                                         AND '2012-01-09 11:56:29.327'                                                      THEN 1 ELSE 0 END                    END            END = 1        AND tl.storenum < 699 

Try the following:

SELECT * FROM emp_master WHERE emp_last_name= CASE emp_first_name  WHEN 'test'    THEN 'test'  WHEN 'Mr name' THEN 'name'END