September 19, 2007 at 10:08 am
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 20, 2007 at 12:41 am
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
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
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.
September 21, 2007 at 3:57 am
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