SQL存储过程实现数据匹配和处理

CREATE PROCEDURE [dbo].[SPU_TrainMatch_Handle_Full] 
-- Add the parameters for the stored procedure here
@mzdate varchar(50),
@addr varchar(50)
AS
BEGIN
-- SET NOCOUNT ON added to prevent extra result sets from
-- interfering with SELECT statements.
SET NOCOUNT ON;

    -- Insert statements for procedure here
    --找比此重车时间晚的最近的未处理的空车数据
declare @pzdate varchar(50)
set @pzdate=null
--select @pzdate=MIN(pzdate) from trainEmptyIndex where pzdate>@mzdate and addr=@addr and statu=0
       select MIN(pzdate) from trainEmptyIndex where pzdate>@mzdate and datediff(hour,convert(datetime,@mzdate),convert(datetime,pzdate))>=1 and addr=@addr and statu=0
--此处修改datediff(hour,convert(datetime,@mzdate),convert(datetime,pzdate))
       --select MIN(pzdate) from trainEmptyIndex where pzdate>@mzdate and datediff(hour, --mzdate,pzdate)>=1 and addr=@addr and statu=0
if(@pzdate is null or @pzdate='')
--没有找到皮重时间
begin 
----找比此重车时间晚的最近的全部的空车数据
set @pzdate=null
select @pzdate=MIN(pzdate) from trainEmptyIndex where pzdate>@mzdate and addr=@addr 
if(@pzdate is not null)
--对此重车进行默认空车处理
begin 
update trainFullInfo set PZ=20.67 where mzdate=@mzdate and addr=@addr
update trainFullIndex set statu=1 where mzdate=@mzdate and addr=@addr
end

end
else
--找了皮重时间
begin 
exec SPU_TrainMatch_FullAndEmpty @mzdate,@addr,@pzdate

--找比重车时间早的重车时间
declare @tblfull table(mzdate varchar(50),addr varchar(50),flag int)
insert into @tblfull select mzdate,addr,0 from trainFullIndex 
where  mzdate<@mzdate and addr=@addr and statu=0
if exists(select 1 from @tblfull)
begin
update trainFullInfo set PZ=20.67 
where mzdate in (select mzdate from @tblfull) and addr=@addr
update trainFullIndex set statu=1 
where mzdate in (select mzdate from @tblfull) and addr=@addr
end
end

insert into tblExistsNewData(isnewdata) select 1
END


GO