CASE statement in where expression in tsql query

Question

I am trying to write a case statement in a where clause for a query that I'm working on. I import the code into Crystal reports, and I'm basically trying to say that the type variable is set to create for this date range in a where else clause that runs for a different date range. He keeps giving me a mistake. I can't seem to determine what is wrong with my syntax. Help someone?

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) 

Answer 1

You do not need a case

 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 insensitive logic in the case . Both conditions have the same meaning for @type . I guess this is a typo.

Answer 2

Using the CASE expression in the where clause is possible, but in general it can be avoided and rewritten with AND/OR . In your case, it will 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) ) 

NEVER 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 that the @type value is not known at compile time, therefore the optimizer does not know whether it will need to search by DateFilled or CreatedOn , therefore it cannot plan to use the index on any of (if one exists), then the table will be scanned regardless available indexes. If you separate the logic using IF/ELSE , it does not matter what the @type value is, a plan is created for each IF branch, and in each branch the optimizer knows which column will be searched, and can plan to use the corresponding 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 , it is much more likely to result in a plan that uses them than when using OR .