存储过程中的查询语句:
select * from songs where S_CommendTime like '%'+isnull(nullif(@S_CommendTime ,''),S_CommendTime)+'%'
and Mark=isnull(nullif(@Mark,''),Mark)
and A_Name like '%'+isnull(nullif(@A_Name,''),A_Name)+'%'
and ......
有6个and但是:如果字段 S_CommendTime 为null @S_CommendTime 传近来null 则会丢数据 Mark为null 一样会丢论坛一位大哥给出了select * from songs where (S_CommendTime like '%'+isnull(nullif(@S_CommendTime ,''),S_CommendTime)+'%' or S_CommendTime is null)
and (Mark=isnull(nullif(@Mark,''),Mark) OR Mark IS null)
and (A_Name like '%'+isnull(nullif(@A_Name,''),A_Name)+'%' Or A_Name is null)
and ......这样的话,当传入参数都为null的时候不会出问题但是,参数不为null的时候,查出来的数据会多出来,库中字段为null的数据..如何解决 谢谢~
select * from songs where S_CommendTime like '%'+isnull(nullif(@S_CommendTime ,''),S_CommendTime)+'%'
and Mark=isnull(nullif(@Mark,''),Mark)
and A_Name like '%'+isnull(nullif(@A_Name,''),A_Name)+'%'
and ......
有6个and但是:如果字段 S_CommendTime 为null @S_CommendTime 传近来null 则会丢数据 Mark为null 一样会丢论坛一位大哥给出了select * from songs where (S_CommendTime like '%'+isnull(nullif(@S_CommendTime ,''),S_CommendTime)+'%' or S_CommendTime is null)
and (Mark=isnull(nullif(@Mark,''),Mark) OR Mark IS null)
and (A_Name like '%'+isnull(nullif(@A_Name,''),A_Name)+'%' Or A_Name is null)
and ......这样的话,当传入参数都为null的时候不会出问题但是,参数不为null的时候,查出来的数据会多出来,库中字段为null的数据..如何解决 谢谢~
改为传null时等于Mark
isnull(@Mark,Mark)
如果空字符和null也等于时
isnull(nullif(isnull(@Mark,''),''),Mark)
null不会与任何值相等
的时候传入 null 时候还会丢掉为null的数据 isnull(nullif(@Mark,''),Mark)
改为传null时等于Mark
isnull(@Mark,Mark) 也会丢掉 Mark为null的数据
isnull(Mark,'')=isnull(nullif(isnull(@Mark,''),''),isnull(Mark,''))
把数据里的null 转为空字符就可以就=号
isnull(Mark,'')=isnull(nullif(isnull(@Mark,''),''),isnull(Mark,''))