ALTER PROCEDURE [ddlib].[ChangePKStopTimeByUID]  AS SET NOCOUNT ON BEGIN   DECLARE
  

  @uid VARCHAR (100),
  

  @startDate VARCHAR (100),
  

  @stopDate VARCHAR (100)
  

  /**   创建游标获取所有用户信息 **/
  

  DECLARE uidsCursor CURSOR     FOR
  

  SELECT top 10 u.uid as uid     FROM UserInfoTest u     WHERE u.IsDelete =  'N' AND u.UnitUserID = 42 and u.uid!=''
  

  FOR UPDATE
  

  PRINT '获取用户信息完毕!准备更新数据'
  

  OPEN uidsCursor
  

  FETCH NEXT FROM uidsCursor
  

  INTO  @uid
  

  while (@uid!='')--(@@fetch_status=0)
  

  begin
  

  IF (len (@uid) = 10)
  

  BEGIN
  

  IF (substring (@uid, 1, 5) = '00061') /**08年之前的10位数的职工**/                               BEGIN
  

  PRINT '正在更新08年之前的10位数的职工'
  

  SET @startDate='19'+substring (@uid, 6, 2)+'0701'                   UPDATE UserInfoTest  SET  StopDate=dateadd(year,120,@startDate) WHERE uid=@uid AND IsDelete='N' AND UnitUserID=42
  

  END
  

  ELSE/**08年之后10位数学号**/
  

  BEGIN
  

  PRINT @uid+'--08年之后10位数学号'
  

  IF (substring (@uid, 5, 2) = '00') /**08年之后本科生**/                       BEGIN
  

  PRINT '正在更新08年之后本科生'
  

  SET @startDate='20'+substring(@uid, 1, 2)+'0701'                            UPDATE UserInfoTest  SET  StopDate=dateadd(year,4,@startDate) WHERE uid=@uid   AND IsDelete='N' AND UnitUserID=42                       END
  

  IF (substring (@uid, 5, 2) = '01') /**08年之后研究生**/                       BEGIN
  

  PRINT '正在更新08年之后研究生'
  

  SET @startDate='20'+substring (@uid, 1, 2)+'0701'                             UPDATE UserInfoTest  SET  StopDate=dateadd(year,2,@startDate) WHERE uid=@uid   AND IsDelete='N' AND UnitUserID=42                                 END
  

  END
  

  END
  

  END
  

  CLOSE uidsCursor     Deallocate uidsCursor
  

  PRINT '更新完成！'
  

  END