ALTER proc UTC_GetMingxiByC
(
@UTC_MingXi_CustCode nvarchar(50),
@UTC_MingXi_State int,
@UTC_MingXi_office nvarchar(30),
@UTC_MingXi_date nvarchar(20)
)
as
select distinct h.UTCid,h.PhoneNo,h.CustCode,h.CustName,convert(varchar(10),h.Rectime,120) as Rectime,
case h.Flag when '0' then '未确定' else '已确定' end as Flag,convert(varchar(10),h.Acctime,120) as Acctime,h.Accuser,d.ArtCode,d.Quantity,a.ArtName,
w.name2
from utc_header as h left join utc_detail as d
on(h.UTCid = d.UTCid) left join utc_article as a
on(d.artcode=a.artcode) left join om01 as o
on(h.CustCode = o.outnum) left join wk.dbo.salesdc_list as w
on(o.outloc = w.outloc)
where (CustCode = isnull(@UTC_MingXi_CustCode ,CustCode))
and (@UTC_MingXi_State = 3 or Flag = @UTC_MingXi_State)
and (@UTC_MingXi_office='ALL' or name2 = @UTC_MingXi_office)
and (convert(varchar(10),Acctime,120) = isnull(@UTC_MingXi_date,convert(varchar(10),Acctime,120)) or Accuser is null)如果这样写 当我输入@UTC_MingXi_date=‘2009-05-13’时候 ,会打印所有Acctime=‘2009-05-13’的值,但是还有 Acctime为空的部分如果这样写 (convert(varchar(10),Acctime,120) = isnull(@UTC_MingXi_date,convert(varchar(10),Acctime,120)))
当@UTC_MingXi_date 为null时候 只答应所有Acctime不为空的部分,但还有那些为空的部分打印不出来,为什么
请大家帮忙,谢谢
ALTER proc UTC_GetMingxiByC
(
@UTC_MingXi_CustCode nvarchar(50),
@UTC_MingXi_State int,
@UTC_MingXi_office nvarchar(30),
@UTC_MingXi_date nvarchar(20)
)
as
set ansi_nulls offselect distinct h.UTCid,h.PhoneNo,h.CustCode,h.CustName,convert(varchar(10),h.Rectime,120) as Rectime,
case h.Flag when '0' then '未确定' else '已确定' end as Flag,convert(varchar(10),h.Acctime,120) as Acctime,h.Accuser,d.ArtCode,d.Quantity,a.ArtName,
w.name2
from utc_header as h left join utc_detail as d
on(h.UTCid = d.UTCid) left join utc_article as a
on(d.artcode=a.artcode) left join om01 as o
on(h.CustCode = o.outnum) left join wk.dbo.salesdc_list as w
on(o.outloc = w.outloc)
where (CustCode = isnull(@UTC_MingXi_CustCode ,CustCode))
and (@UTC_MingXi_State = 3 or Flag = @UTC_MingXi_State)
and (@UTC_MingXi_office='ALL' or name2 = @UTC_MingXi_office)
and (convert(varchar(10),Acctime,120) = isnull(@UTC_MingXi_date,convert(varchar(10),Acctime,120))) set ansi_nulls on
那你的语句在设计上是正确的。你要显示Accuser 为空的当然也会牵连到acctime为空的
ALTER proc UTC_GetMingxiByC
(
@UTC_MingXi_CustCode nvarchar(50),
@UTC_MingXi_State int,
@UTC_MingXi_office nvarchar(30),
@UTC_MingXi_date nvarchar(20)
)
as
IF @UTC_MingXi_date IS NOT NULL
select distinct h.UTCid,h.PhoneNo,h.CustCode,h.CustName,convert(varchar(10),h.Rectime,120) as Rectime,
case h.Flag when '0' then '未确定' else '已确定' end as Flag,convert(varchar(10),h.Acctime,120) as Acctime,h.Accuser,d.ArtCode,d.Quantity,a.ArtName,
w.name2
from utc_header as h left join utc_detail as d
on(h.UTCid = d.UTCid) left join utc_article as a
on(d.artcode=a.artcode) left join om01 as o
on(h.CustCode = o.outnum) left join wk.dbo.salesdc_list as w
on(o.outloc = w.outloc)
where (CustCode = isnull(@UTC_MingXi_CustCode ,CustCode))
and (@UTC_MingXi_State = 3 or Flag = @UTC_MingXi_State)
and (@UTC_MingXi_office='ALL' or name2 = @UTC_MingXi_office)
and (convert(varchar(10),Acctime,120) = CONVERT(VARCHAR(10),@UTC_MingXi_date,120))
ELSE
select distinct h.UTCid,h.PhoneNo,h.CustCode,h.CustName,convert(varchar(10),h.Rectime,120) as Rectime,
case h.Flag when '0' then '未确定' else '已确定' end as Flag,convert(varchar(10),h.Acctime,120) as Acctime,h.Accuser,d.ArtCode,d.Quantity,a.ArtName,
w.name2
from utc_header as h left join utc_detail as d
on(h.UTCid = d.UTCid) left join utc_article as a
on(d.artcode=a.artcode) left join om01 as o
on(h.CustCode = o.outnum) left join wk.dbo.salesdc_list as w
on(o.outloc = w.outloc)
where (CustCode = isnull(@UTC_MingXi_CustCode ,CustCode))
and (@UTC_MingXi_State = 3 or Flag = @UTC_MingXi_State)
and (@UTC_MingXi_office='ALL' or name2 = @UTC_MingXi_office)
当@UTC_MingXi_date 为null时候 只答应所有Acctime不为空的部分,但还有那些为空的部分打印不出来,为什么
2、实际上就是convert(varchar(10),Acctime,120)=convert(varchar(10),Acctime,120),你看一下这个就知道了。
null和null是不能比较的,如果那个字段为空的话。
if convert(varchar(10),null,120)=convert(varchar(10),null,120)
print '='
else
print '!='
/*
!=
*/