修改学籍异动的storeprocedure

修改原因：

 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

转载于:https://www.cnblogs.com/samsonleung/archive/2008/10/24/1318702.html