Problem Using if condition in select stored procesure

Hi!
how can i use if condition in where clause of select statement
I want the work like this
but this is not working correct
select * from Table1
where
if (@.a>1)
field1=@.a
else
field2=@.a
This is not a recommended usage since the optimizer will probably not
generate a good plan. It would be better to use a IF statement and two
select statements. In addition, I find the logic more than a bit strange.
In any event, below is an example
use Northwind
go
--select * from invoices
declare @.a int
/*set @.a = 14
select * from invoices
where ProductID = @.a
set @.a = 10249
select * from invoices
where OrderID = @.a*/
set @.a = 10249
set @.a = 14
select * from invoices
where case when @.a > 1000 then OrderID else @.a end = @.a
and case when @.a <= 1000 then ProductID else @.a end = @.a
"microsoft" <shahzad@.procuresoft.com> wrote in message
news:enf58S05EHA.2964@.TK2MSFTNGP09.phx.gbl...
> Hi!
> how can i use if condition in where clause of select statement
> I want the work like this
> but this is not working correct
> select * from Table1
> where
> if (@.a>1)
> field1=@.a
> else
> field2=@.a
>
|||select * from Table1 where @.a>1 and field1=@.a
Union
select * from Table1 where @.a<1 and field2=@.a
"microsoft" wrote:

> Hi!
> how can i use if condition in where clause of select statement
> I want the work like this
> but this is not working correct
> select * from Table1
> where
> if (@.a>1)
> field1=@.a
> else
> field2=@.a
>
>
|||where
(@.a > 1 and field1 = @.a) or (@.a <= 1 and field2 = @.a)
or (probably less efficient)
where
@.a = case when @.a > 1 then field1 else field2 end
If you have a non-ansi setting for null comparisons,
where
(@.a > 1 and field1 = @.a) or ((@.a <= 1 or @.a is null) and field2 = @.a)
Steve Kass
Drew University
microsoft wrote:

>Hi!
>how can i use if condition in where clause of select statement
>I want the work like this
>but this is not working correct
>select * from Table1
> where
> if (@.a>1)
> field1=@.a
> else
> field2=@.a
>
>