CASE statement in where clause in tsql query

Question

I'm trying to write a case statement in the where clause for a query I am working on. I am importing the code in Crystal reports and I am basically trying to say if the variable 'type' is set to 'create' run for this date range in the where clause else run for a different date range. It keeps giving me an error. I can't seem to identify what is wrong with my syntax here. Help anyone?

DECLARE @Date1 DATETIME
DECLARE @Date2 DATETIME
DECLARE @type VARCHAR(20)
SET @Date1 = '2010-1-1'
SET @Date2 = '2010-2-1'
SET @type = '{?DateType}'

select *
from filled
WHERE   
    (CASE WHEN @type = 'create' THEN 
    filled.CREATEDON >= @Date1
    AND filled.CREATEDON < DATEADD(d, +1, @Date2)
    WHEN @type <> 'create' THEN   
    filled.datefilled >= @Date1
     AND filled.datefilled < DATEADD(d, +1, @Date2) 
     END)

Solution 1

You don't need a case statement

WHERE ( (@type = 'create' and filled.CREATEDON >= @Date1 AND filled.CREATEDON < DATEADD(d, +1, @Date2) ) or
        (@type <> 'create' and filled.datefilled >= @Date1 AND filled.datefilled < DATEADD(d, +1, @Date2) ) 
      )

This leaves the non-sensical logic you have in the casestatement. Both conditions have the same value for @type. I assume that is a typo.

Solution 2

Using a CASE expression in a where clause is possible, but generally it can be avoided, and rewritten using AND/OR, IN your case it would be:

WHERE(  @Type = 'create' 
    AND filled.CREATEDON >= @Date1 
    AND filled.CREATEDON < DATEADD(d, +1, @Date2)
    )
OR  (   @Type != 'create' 
    AND filled.datefilled >= @Date1
    AND filled.datefilled < DATEADD(d, +1, @Date2) 
    )

HOWEVER queries like this usually produce suboptimal plans. You should use IF/ELSE logic if possible:

IF @Type = 'create'
BEGIN
    SELECT  *
    FROM    Filled
    WHERE   Filled.CreatedOn >= @Date1
    AND     Filled.CreatedOn < DATEADD(DAY, 1, @Date2)
END
ELSE
BEGIN
    SELECT  *
    FROM    Filled
    WHERE   Filled.DateFilled >= @Date1
    AND     Filled.DateFilled < DATEADD(DAY, 1, @Date2)
END

The reason for this is the value of @type is not known at compile time, therefore the optimiser does not know whether it will need to search on DateFilled or CreatedOn, therefore cannot plan to use an index on either column (if one exists), so will do a table scan regardless of the indexes available. Whereas if you separate the logic with IF/ELSE it does not matter what the value of @type is, a plan is created for each branch of the IF, and in each branch the optimiser knows which column will be searched, and can plan to use the appropriate index.

You can also use UNION ALL:

SELECT  *
FROM    Filled
WHERE   Filled.CreatedOn >= @Date1
AND     Filled.CreatedOn < DATEADD(DAY, 1, @Date2)
AND     @Type = 'create'
UNION ALL
SELECT  *
FROM    Filled
WHERE   Filled.DateFilled >= @Date1
AND     Filled.DateFilled < DATEADD(DAY, 1, @Date2)
AND     @Type <> 'create';

Again, if indexes exist on DateFilled or CreatedOn this is much more likely to produce a plan that uses them than using OR.