Post reply

CASE in WHERE Clause

  • September 19, 2007 at 10:08 am

    #198026

    I have a where clause that I am trying to write that needs to vary depending on the value of a variable, but I can't seem to get it to work. Is this even possible? I'll paste what I have so far below:

    WHERE

    (@Value IS NULL OR (@Value = ppl.PricingLevelID)) AND

    (CASE @Value

    WHEN NULL THEN

    (@NegOHQCost = 'N' OR (@NegOHQCost = 'Y' AND (ip.OnHandQty < 0 OR ip.AverageCost < 0))) AND
    (@ZeroCost = 'N' OR (@ZeroCost = 'Y' AND ip.OnHandQty > 0 AND ip.AverageCost = 0))

    ELSE (@NegOHQCost = 'N' OR (@NegOHQCost = 'Y' AND (ip.OnHandQty < 0 OR ppl.Amount < 0))) AND (@ZeroCost = 'N' OR (@ZeroCost = 'Y' AND ip.OnHandQty > 0 AND ppl.Amount = 0))

    END) AND

    ....

  • September 19, 2007 at 11:53 am

    #734675

    You may want to a take a look at this script.

     

  • September 20, 2007 at 12:41 am

    #734833

    Perhaps you should consider rewriting your query, because i dont think this is the fastet way to do it. I noticed you used a case.. You can also use and (@value is null and .....) or (@value is not null and .... ) then you do not need the case...

    Note

    Using Or isnt really fast too

  • September 20, 2007 at 3:30 am

    #734868

    We had a query that did this sort of stuff using the "where @value is null and..." syntax, and the performance was really nasty.  If you can, it's much better to do this:

    if @value is null

        select...

        where...

    else

        select...

        where...

  • September 20, 2007 at 8:27 am

    #734954

    I have to agree with Andrew's comment on using the IF...ELSE statement. 

    You repeat your search for @Value IS NULL twice, first right after the WHERE clause and secondly as your condition for your CASE statement.  That only makes SQL Server work so much harder to find the results you want.  Only use your @Value check once.  If you want to stick with a CASE, then stick the check in the conditional check part, not above the CASE.

    Also, I've noticed that you should always display your Execution plans when coding with CASE and IF...ELSE.  Sometimes CASE works better than IF, other times IF works better than CASE.  Depends on what you're doing.  And if you look at your execution plans as you're developing, you'll find which one works better and be able to code accordingly.

    Brandie Tarvin, MCITP Database AdministratorLiveJournal Blog: http://brandietarvin.livejournal.com/[/url]On LinkedIn!, Google+, and Twitter.Freelance Writer: ShadowrunLatchkeys: Nevermore, Latchkeys: The Bootleg War, and Latchkeys: Roscoes in the Night are now available on Nook and Kindle.

  • September 21, 2007 at 3:57 am

    #735161

    You might also wish to post your entire query and explain how it is used and for what. There is a good chance that the SQL can be rewritten for better performance, and someone could give you pointers on how to do it.

Viewing 6 posts - 1 through 5 (of 5 total)

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