好久没写代码，今天刚刚给客户写了点

真郁闷，这家客户自己写外挂把我们的数据库完整性给破坏了，还要我们修复

唉，贴出来做个纪念吧......

declare   @cardnum   nvarchar ( 80 )
declare   @personid   nvarchar ( 80 )
declare   @accessionstate   int
declare   @personid222   nvarchar ( 80 )
declare  _cor  cursor   for  
select  cardnum  as  cn  from  psnaccount 
where  cardnum  <> ''   group   by  cardnum
having   count (cardnum) > 1    order   by  cn  desc  

open  _cor
FETCH   NEXT   FROM  _cor  INTO   @cardnum

    WHILE   @@FETCH_STATUS   =   0
    BEGIN

       PRINT   '    '   +   @cardnum
     set   @personid   =   ''
     set   @personid222   =   ''
     /**/ /* 查询出离职人员编号  */
     declare  personid_cursor  cursor   for
     select   top   1  personid,accessionstate  from  psnaccount  where  cardnum  =   @cardnum   and  accessionstate  =   4
     open  personid_cursor
     FETCH   next   from  personid_cursor  into   @personid , @accessionstate
         WHILE   @@FETCH_STATUS   =   0
            BEGIN
             fetch   next   from  personid_cursor  into   @personid , @accessionstate
         END
     close  personid_cursor
     deallocate  personid_cursor
     /**/ /* END 查询出离职人员编号  */

     /**/ /* 查询出在职人员编号 */
     declare  personid_cursor  cursor   for
     select   top   1  personid,accessionstate  from  psnaccount  where  cardnum  =   @cardnum   and  accessionstate  =   2
     open  personid_cursor
     FETCH   next   from  personid_cursor  into   @personid222 , @accessionstate
         WHILE   @@FETCH_STATUS   =   0
            BEGIN
             fetch   next   from  personid_cursor  into   @personid222 , @accessionstate
         END
     close  personid_cursor
     deallocate  personid_cursor
     /**/ /* END 查询出在职人员编号  */

     /**/ /* 更新编号 */
     if   @personid   <> ''   and   @personid222   <> ''
     BEGIN
         UPDATE  atdtimecarddata  set  PERSONID  =   @personid222   where  personid  =   @personid   and  receivedate  >= ' 2005-09-12 '
     END
     /**/ /* END 更新编号 */

       FETCH   NEXT   FROM  _cor  INTO   @cardnum
   
    END
CLOSE  _cor
DEALLOCATE  _cor

转载于:https://www.cnblogs.com/minbear/archive/2005/09/28/246084.html