title: "CASE" statement within "WHERE" clause in SQL Server 2008
body: <p>I am working with a query which contains "CASE" statement within "WHERE" clause. But SQL Server 2008 is giving some errors while executing it. Can anyone please help me with the correct query? Here is the query:</p>
  
  <pre><code>SELECT
      tl.storenum 'Store #', 
      co.ccnum 'FuelFirst Card #', 
      co.dtentered 'Date Entered',
      CASE st.reasonid 
          WHEN 1 THEN 'Active' 
     WHEN 2 THEN 'Not Active' 
     WHEN 0 THEN st.ccstatustypename 
     ELSE 'Unknown' 
      END 'Status',
      CASE st.ccstatustypename 
          WHEN 'Active' THEN ' ' 
     WHEN 'Not Active' THEN ' ' 
     ELSE st.ccstatustypename 
      END 'Reason',
      UPPER(REPLACE(REPLACE(co.personentered,'RT\\\\',''),'RACETRAC\\\\','')) 'Person Entered',
      co.comments 'Comments or Notes'
  FROM 
      comments co
      INNER JOIN cards cc ON co.ccnum=cc.ccnum
      INNER JOIN customerinfo ci ON cc.customerinfoid=ci.customerinfoid
      INNER JOIN ccstatustype st ON st.ccstatustypeid=cc.ccstatustypeid
      INNER JOIN customerstatus cs ON cs.customerstatuscd=ci.customerstatuscd
      INNER JOIN transactionlog tl ON tl.transactionlogid=co.transactionlogid
      LEFT JOIN stores s ON s.StoreNum = tl.StoreNum
  WHERE 
      CASE LEN('TestPerson')
          WHEN 0 THEN co.personentered  = co.personentered
     ELSE co.personentered LIKE '%TestPerson'
      END 
      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 co.DTEntered = co.DTEntered 
     ELSE 
         CASE LEN('2012-01-09 11:56:29.327') 
             WHEN 0 THEN co.DTEntered &gt;= '2011-01-09 11:56:29.327' 
        ELSE co.DTEntered BETWEEN '2011-01-09 11:56:29.327' AND '2012-01-09 11:56:29.327' 
         END 
      END
      AND tl.storenum &lt; 699 
  ORDER BY tl.StoreNum
  </code></pre>