Using a if or Case in WHERE clause as second condition


  • Hi Folks,

    I'm trying to extract some records from a table depending on some conditions

    I need to extra all records where de code is in  A through E and all persons between age 0 and 120

    but, when the code =  E then i can't check ages

    So i thought the WHERE clause should be something like

    WHERE  LEFT(Code,1) IN ('A','B','C','D','E')

    AND IF Code <> 'E' Then DateDiff(yyyy, C.Geboortedatum, dbo.ufn_CreateDate(2013, 4)) BETWEEN 0 AND 120

    unfortunately this is not working. Is there someone who can help me on this?

    Best regards


    Friday, July 12, 2013 11:52 AM


  • You're making it too complicated.  Your conditions are logically: 

    rows where code = E
    rows where code in A ~ D and age between 0 and 120

    That should give you an idea about how to formulate your where clause.  That is one way, and I can think of another involving a case expression.  Note that case is not a control-of-flow construct in tsql.  It is an expression that returns a scalar value.  So you could try something like:

    ... where (case Code when E then 0 else datediff ... end) between 0 and 120

    Try it both ways and review the execution plan to see which one is more efficient. 

    Friday, July 12, 2013 1:20 PM