question

asmasm avatar image
asmasm asked

use case in where clause

Dear All I want to merge the below statements in one querry select * from table1 where col1=@col select * from table1 where col2=@col I want to use third variable and use case statement to handle both conditions
sql-server-2008t-sqlcase-statement
2 comments
10 |1200

Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.

Not quite sure what you're driving at here. Could you explain in a bit more detail?
0 Likes 0 ·
just like select * from table where case when cond1=@cond1 then col1=@col1 else col2=@col2
0 Likes 0 ·
David Wimbush avatar image
David Wimbush answered
Presumably the 3rd variable says whether to compare with col1 or col2. So you can do it like this: select * from table1 where (@colNo = 1 and col1 = @col) or (@colNo = 2 and col2 = @col);
10 |1200

Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.

David Wimbush avatar image
David Wimbush answered
OK. In that case I think it should be: select * from table1 where (cond1 = @cond1 and col1 = @col1) or (cond1 @cond1 and col2 = @col2);
2 comments
10 |1200

Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.

Shouldn't that be... where (cond1 = @cond1 and col1 = @col1) or (con1 @cond1 and col2 = @col2) After all, there may be cases where the col2 match applies if the cond1 also applies, and that might not be what the OP requires... just a thought
2 Likes 2 ·
Well done for spotting my deliberate mistake ;) Seriously though, yes you're right. I've corrected it in case anyone in the future doesn't read the whole trail.
1 Like 1 ·
asmasm avatar image
asmasm answered
the problem is still there any solution??
1 comment
10 |1200

Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.

You didn't mention a problem before. You're going to have to explain better if you want an answer.
0 Likes 0 ·
asmasm avatar image
asmasm answered
i want to switch between two conditions in the where clause keeping select statement the same like select * from table1 where col1=@col1 and select * from table1 where col2=@col2 i want to merge it in one query 2nd solution of this problem is if @cond=1 select * from table1 where col1=@col1 else select * from table1 where col2=@col2 but i want this to achieve using case statement in where clause
10 |1200

Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.

David Wimbush avatar image
David Wimbush answered
Right. This should do the trick: select * from table1 where (@cond=1 and col1=@col1) or (@cond1 and col2=@col2); I don't know why you keep insisting on a CASE statement. You might be able to use one in a WHERE clause. I don't know. But this should work fine.
10 |1200

Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.

Write an Answer

Hint: Notify or tag a user in this post by typing @username.

Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.