October 24, 2009 at 6:06 pm
I am sorry to ask this question since there must be tons of posting on the web but I cannot find any that answer. Can anyone please show me the most elegant way to write a case when where depending on a boolean value, the filter in the WHERE clause would include a set of values. I hope the code sample belows explains what I am looking for. Let me know if you have any questions. Thank you.
declare @result table
(
[id] char(1)
,[filterid] int
)
declare @flag bit
set @flag = 1
insert into @result select 'A', 1
insert into @result select 'B', 2
insert into @result select 'C', 3
insert into @result select 'D', 4
-- how do you write a query that simulates the following?
select
*
from
@result
where
filterid = case when @flag = 0 then 2 else (1,3,4) end
-- output desired
-- when @flag = 0
-- idfilterid
-- B2
-- output desired
-- when @flag = 1
-- idfilterid
-- A1
-- C3
-- D4
"Any fool can write code that a computer can understand. Good programmers write
code that humans can understand." -Martin Fowler et al, Refactoring: Improving the Design of Existing Code, 1999
October 24, 2009 at 9:00 pm
You could do it in dynamic SQL and probably get better performing results, but otherwise it is as simple as
WHERE @flag = 0 and filter = 2
or @flag !=0 and filter in (1,3,4)
I don't like this approach because I believe you will get a suboptimal query plan (table scan). You might get better results with something like this, but I haven't tested it to see if it would make a difference.
WHERE filter in (1,2,3,4)
and ((filter = 2 and @flag = 0) or (filter in (1,3,4) and @flag != 0))
__________________________________________________
Against stupidity the gods themselves contend in vain. -- Friedrich Schiller
Stop, children, what's that sound? Everybody look what's going down. -- Stephen Stills
October 24, 2009 at 9:40 pm
That's perfect. Thank you very much.
"Any fool can write code that a computer can understand. Good programmers write
code that humans can understand." -Martin Fowler et al, Refactoring: Improving the Design of Existing Code, 1999
Viewing 3 posts - 1 through 2 (of 2 total)
You must be logged in to reply to this topic. Login to reply