October 12, 2006 at 8:03 am
Is there a better way to do this WHERE statement?
DECLARE @SpreadSelect as int
SELECT @SpreadSelect = 0
SELECT AcctNo
FROM _FinalFlat
WHERE
(SpreadFile = CASE
WHEN @SpreadSelect = 0 THEN 1 ELSE
2 END
OR
SpreadFile < CASE
WHEN @SpreadSelect = 0 THEN 9 ELSE
0 END)
October 12, 2006 at 11:52 am
DECLARE @SpreadSelect as int
SELECT @SpreadSelect = 0
IF @SpreadSelect = 0
SELECT AcctNo
FROM _FinalFlat
WHERE
(SpreadFile IN (1, 9) )
ELSE
SELECT AcctNo
FROM _FinalFlat
WHERE
(SpreadFile IN (0, 2) )
Mark
October 13, 2006 at 3:20 am
DECLARE @SpreadSelect as int
SELECT @SpreadSelect = 0
SELECT AcctNo
FROM _FinalFlat
WHERE (@SpreadSelect = 0 AND SpreadFile in (1, 9))
OR (@SpreadSelect <> 0 AND SpreadFile in (2,0))
I'd check the execution plan to see whether my or Mark's versions (above) work most efficiently on your data. I suspect that for ultimate performance, start with Mark's query as a base but put each of the two select statements into separate stored procedures, with a parent stored procedure which doesn't do any data access, just checks the conditions and calls the appropriate child stored procedure. In pseudo-code:
StoredProc1 -
SELECT AcctNo
FROM _FinalFlat
WHERE SpreadFile IN (1, 9)
StoredProc2 -
SELECT AcctNo
FROM _FinalFlat
WHERE SpreadFile IN (0, 2)
StoredProc3
Declare @SpreadSelect as an input parameter
if @SpreadSelect = 0 then exec StoredProc1 else exec StoredProc2
Your code would only ever call StoredProc3. This has the advantage of the actual application plan for each of the two queries being able to be cached. Having them all in one stored procedure with an 'if' statement deciding which one is called means the execution plan has to be recreated each time the procedure is called with a different parameter. Then again, you may be able to get away with my single select statement which could be kept in a single stored procedure. Check the execution plans and compare the performance.
October 13, 2006 at 4:30 am
OR (@SpreadSelect = 0 AND SpreadFile < 9)
Regards,Iain
October 13, 2006 at 5:56 am
I believe that the SQL Query optimizer is smart enough to build and cache a single plan when the queries are the same or similar, even with an IF statement. So my version with two queries in an IF..ELSE statement will not be dynamic sql, but will be a single plan.
And I also missed the "<" in the original post. Here is my revised suggested query:
IF @SpreadSelect = 0 SELECT AcctNo FROM _FinalFlat WHERE (SpreadFile < 9) )
ELSE SELECT AcctNo FROM _FinalFlat WHERE (SpreadFile < 0 OR SpreadFile = 2) )
Mark
October 17, 2006 at 5:19 am
With '<' condition Mark's solution should work perfect for your requirement.
If you want to go with the IN Clause this may work for you..
DECLARE @SpreadSelect as int
SELECT @SpreadSelect = 0
SELECT AcctNo
FROM
_FinalFlat
WHERE
SpreadFile IN ((CASE WHEN @SpreadSelect = 0
THEN 1
ELSE
0
END), (CASE WHEN @SpreadSelect =0
THEN
9
ELSE
2
END))
Prasad Bhogadi
www.inforaise.com
Viewing 6 posts - 1 through 5 (of 5 total)
You must be logged in to reply to this topic. Login to reply