Post reply

?? on a CASE within a WHERE ??

  • March 4, 2015 at 2:32 pm

    #305051

    Hi

    I'm trying to put something like below within a where clause.

    I have a parameter @Units which can have one of the values on YES, NO or BOTH

    The Idea is if @Units = Yes I only want USER_DEFINED_DATA.CAD213 = 'on'

    if @Units = No I only want USER_DEFINED_DATA.CAD214 = 'on'

    IF @UNITS = 'Both' .... I want both

    Thanks

    Joe

    WHERE ...

    and

    @Units = CASE

    WHEN @Units = 'Yes' THEN (USER_DEFINED_DATA.CAD213 = 'on' )

    WHEN @Units = 'No' THEN (USER_DEFINED_DATA.CAD214 = 'on' )

    END

    end

  • March 4, 2015 at 2:49 pm

    #1781784

    WHERE

    ((@Units = 'Yes' AND USER_DEFINED_DATA.CAD213 = 'on') OR

    (@Units = 'No' AND USER_DEFINED_DATA.CAD214 = 'on') OR

    (@Units = 'Both' AND USER_DEFINED_DATA.CAD213 = 'on' AND

    USER_DEFINED_DATA.CAD214 = 'on'))

    SQL DBA,SQL Server MVP(07, 08, 09) "It's a dog-eat-dog world, and I'm wearing Milk-Bone underwear." "Norm", on "Cheers". Also from "Cheers", from "Carla": "You need to know 3 things about Tortelli men: Tortelli men draw women like flies; Tortelli men treat women like flies; Tortelli men's brains are in their flies".

  • March 4, 2015 at 3:05 pm

    #1781789

    Thanks Scott,

    I could get my head around it, I started doing it with if statements around selects, but this is much cleaner..

    Thanks

    Again

Viewing 3 posts - 1 through 2 (of 2 total)

You must be logged in to reply to this topic. Login to reply