September 22, 2003 at 4:41 pm
set ansi_nulls off
go
create table t1(
f1 varchar(10) null,
f2 varchar(10) null)
go
insert into t1
select 'a','a1'
union
select 'b','b1'
union
select 'c',null
go
select * from t1 where 1=1
will return all 3 rows, but
select * from t1 where f2=f2
only return the first two rows, why the f2=null row missing.
what I want to do is
select * from t1 where
f1=isnull(@x1, f1)
and
f2=isnull(@x2,f2)
if I pass not null @x1 or @x2, I want the specefic rows returned, if @x1 or @x2 =null, I did not want any restriction on the where contition(all rows should be returned). I can do this in dynamic query using case and get correct result, just wonder it is possible using rowset query to write a simple query to do this.
September 22, 2003 at 11:43 pm
Try the following query. This would work.
begin
Declare @x1 as varchar(5)
Declare @x2 as varchar(5)
set @x1=null
set @x2=null
select *
from
t1
where
isnull(f1,'')=isnull(isnull(@x1, f1),'') and
isnull(f2,'') = isnull(isnull(@x2,f2),'')
end
In a query when you try to compare two NULL values, the expression always evaluates to false. This is why the query that you tried skipped the 3rd row where f2 is NUll.
September 23, 2003 at 9:41 am
joelucas
Thank you very much, this is a very smart solution!
Viewing 3 posts - 1 through 2 (of 2 total)
You must be logged in to reply to this topic. Login to reply