CASE statement in where clause in tsql query

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)

  • SQL How to convert this to a SubQuery?
  • insert data into several tables - possibly using OUTPUT - sql server 2005
  • Unable to connect to SQL LocalDB
  • SQL- Count occurrences of a specific word within all stored procedures
  • The conversion of the varchar value overflowed an int column
  • SQL Server trigger for insert or updated
  • 2 Solutions collect form web for “CASE statement in where clause in tsql query”

    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.

    Using a CASE statement 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.

    MS SQL Server is a Microsoft SQL Database product, include sql server standard, sql server management studio, sql server express and so on.