select * from A
where
Location<= case when @LocTo='' then (select max(Location) from A) else @LocTo end
and
Location>= case when @Loc ='' then (select min(Location) from A) else @Loc end
where
Location<= case when @LocTo='' then (select max(Location) from A) else @LocTo end
and
Location>= case when @Loc ='' then (select min(Location) from A) else @Loc end
---then 後面不能接上表達式 例如 a=4,a*b等
但可以增加一個常量(有唯一確定值
例如
select min(Location) from A
4
Declare @Loc char(4)
set @Loc=''
Declare @LocTo char(4) --nqjAdd
set @LocTo=''
if( (@loc='' or @loc is null) and (@locTo='' or @locTo is null))
select * from A
else if((@loc<>'' or @loc not is null) and (@locTo='' or @locTo is null))
select * from A where Location > @Loc
else if((@loc='' or @loc is null) and (@locTo<>'' or @locTo not is null))
select * from A where Location < @Loc
else if ((@loc<>'' or @loc not is null) and (@locTo<>'' or @locTo not is null))
select * from A where Location < @Loc and Location > @Loc