修改原因:
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