“CASE” statement within “WHERE” clause in SQL Server 2008

Question

I am working with a query that contains the "CASE" statement within the "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:

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 >= '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 < 699 

ORDER BY tl.StoreNum

Answer 1

You can use this:

WHERE co.DTEntered = CASE 

                          WHEN LEN('blah') = 0 

                               THEN co.DTEntered 

                          ELSE '2011-01-01' 

                     END 

But it will not work the way you have mentioned them:

Example:

WHERE 

    CASE LEN('TestPerson')

        WHEN 0 THEN co.personentered  = co.personentered

   ELSE co.personentered LIKE '%TestPerson'

    END 

You can try using combined OR statements like this:

WHERE (

        (LEN('TestPerson') = 0 

             AND co.personentered = co.personentered

        ) 

        OR 

        (LEN('TestPerson') <> 0 

             AND co.personentered LIKE '%TestPerson')

      )

These types of shenanigans in a WHERE clause will often prevent the query optimizer from utilizing indexes.

Note: The CASE statement must be part of the expression, not the expression itself.

You can learn in-depth about SQL statements, queries and become proficient in SQL queries by enrolling in our industry-recognized SQL online course.