修改原因:
ydid总是有重复值,原来系统不是直接在xjydjzb中更新数据,而是在一个keytable中记录上一次的最大的ydid。--今天在错误信息反馈中才知道这个原理 。
所以直接在keytable中找ydid的最大值。
CREATE procedure sp_xjyd
@xhjm char(10),@yddm char(2)
as
if exists(select xhjm from xsjbdab where xhjm=@xhjm)
begin
declare @ydid varchar(20),@xh varchar(20),@bdm varchar(20),@xslxdm varchar(20),@xslbdm varchar(20)
declare @nj varchar(20),@xxjdm varchar(20),@yxjdm varchar(20),@jhid varchar(20),@sxdm varchar(20)
declare @fxdm varchar(20),@gzzdm varchar(20),@ydlxbz varchar(20)
declare @czn varchar(20),@czy varchar(20),@czr varchar(20),@dyr varchar(20)
declare @zyydbj varchar(3),@sxydbj varchar(3),@fxydbj varchar(20),@ydqxhqm varchar(20),@ybyzg varchar(20),@xbyzg varchar(20)
declare @day datetime
set @day=getdate()
select @ydid=cast((max(table_id)+1) as varchar(20)) from keytable where table_name='xjydjzb'
if @yddm='31'
set @xxjdm='5'
else if @yddm='21'
set @xxjdm='3'
else if @yddm='12'
set @xxjdm='1'
set @dyr='LJC'
select @xh=xh,@bdm=bdm,@xslxdm=xslxdm,@xslbdm=xslbdm,@nj=nj,@yxjdm=xjztdm,@jhid=jhid,@sxdm=dwdm,
@fxdm=left(dwdm,5),@gzzdm=left(dwdm,3),@ydlxbz=@yddm,@czn=datepart(YYYY,@day),
@czy=datepart(MM,@day),@czr=datepart(DD,@day),@fxydbj='2',@ydqxhqm=xh,
@ybyzg='Y',@xbyzg='N',@zyydbj='N',@sxydbj='Y'
from xsjbdab
where xhjm=@xhjm
--bz,ydqxhjm不设值
insert into xjydjzb(ydid,xh,xbdm,xxslxdm,xxslbdm,xnj,xxjdm,xjijhid,xsxdm,xfxdm,xgzzdm,ybdm,yxslxdm
,yxslbdm,ynj,yxjdm,yjxjhid,ysxdm,yfxdm,ygzzdm,ydlxbz,czn,czy,czr,dyr,zyydbj,sxydbj,fxydbj,ydqxhqm,ybyzg,xbyzg)
values(@ydid,@xh,@bdm,@xslxdm,@xslbdm,@nj,@xxjdm,@jhid,@sxdm,@fxdm,@gzzdm,@bdm,@xslxdm
,@xslbdm,@nj,@yxjdm,@jhid,@sxdm,@fxdm,@gzzdm,@ydlxbz,@czn,@czy,@czr,@dyr,@zyydbj,@sxydbj,
@fxydbj,@ydqxhqm,@ybyzg,@xbyzg)
update xsjbdab
set xjztdm=@xxjdm
where xhjm=@xhjm
update keytable
set table_id=cast(@ydid as numeric(9)) where table_name='xjydjzb'
end
else
print 'xhjm not exist!'
GO
@xhjm char(10),@yddm char(2)
as
if exists(select xhjm from xsjbdab where xhjm=@xhjm)
begin
declare @ydid varchar(20),@xh varchar(20),@bdm varchar(20),@xslxdm varchar(20),@xslbdm varchar(20)
declare @nj varchar(20),@xxjdm varchar(20),@yxjdm varchar(20),@jhid varchar(20),@sxdm varchar(20)
declare @fxdm varchar(20),@gzzdm varchar(20),@ydlxbz varchar(20)
declare @czn varchar(20),@czy varchar(20),@czr varchar(20),@dyr varchar(20)
declare @zyydbj varchar(3),@sxydbj varchar(3),@fxydbj varchar(20),@ydqxhqm varchar(20),@ybyzg varchar(20),@xbyzg varchar(20)
declare @day datetime
set @day=getdate()
select @ydid=cast((max(table_id)+1) as varchar(20)) from keytable where table_name='xjydjzb'
if @yddm='31'
set @xxjdm='5'
else if @yddm='21'
set @xxjdm='3'
else if @yddm='12'
set @xxjdm='1'
set @dyr='LJC'
select @xh=xh,@bdm=bdm,@xslxdm=xslxdm,@xslbdm=xslbdm,@nj=nj,@yxjdm=xjztdm,@jhid=jhid,@sxdm=dwdm,
@fxdm=left(dwdm,5),@gzzdm=left(dwdm,3),@ydlxbz=@yddm,@czn=datepart(YYYY,@day),
@czy=datepart(MM,@day),@czr=datepart(DD,@day),@fxydbj='2',@ydqxhqm=xh,
@ybyzg='Y',@xbyzg='N',@zyydbj='N',@sxydbj='Y'
from xsjbdab
where xhjm=@xhjm
--bz,ydqxhjm不设值
insert into xjydjzb(ydid,xh,xbdm,xxslxdm,xxslbdm,xnj,xxjdm,xjijhid,xsxdm,xfxdm,xgzzdm,ybdm,yxslxdm
,yxslbdm,ynj,yxjdm,yjxjhid,ysxdm,yfxdm,ygzzdm,ydlxbz,czn,czy,czr,dyr,zyydbj,sxydbj,fxydbj,ydqxhqm,ybyzg,xbyzg)
values(@ydid,@xh,@bdm,@xslxdm,@xslbdm,@nj,@xxjdm,@jhid,@sxdm,@fxdm,@gzzdm,@bdm,@xslxdm
,@xslbdm,@nj,@yxjdm,@jhid,@sxdm,@fxdm,@gzzdm,@ydlxbz,@czn,@czy,@czr,@dyr,@zyydbj,@sxydbj,
@fxydbj,@ydqxhqm,@ybyzg,@xbyzg)
update xsjbdab
set xjztdm=@xxjdm
where xhjm=@xhjm
update keytable
set table_id=cast(@ydid as numeric(9)) where table_name='xjydjzb'
end
else
print 'xhjm not exist!'
GO