首页 新闻 搜索 专区 学院

sql 游标在FETCH NEXT 的时候退出了循环,这是怎么回事????????

0
悬赏园豆:15 [已关闭问题] 关闭于 2011-12-07 11:47

 --这个触发器


ALTER TRIGGER [TGR_TA_WS_ZT]
ON [dbo].[SYS_YSBZBBZT] FOR UPDATE AS

BEGIN
 DECLARE @BBBH VARCHAR(40);--报表编号
 DECLARE @VERSION VARCHAR(40);--版本
 DECLARE @ZBID VARCHAR(40);--指标id
 DECLARE @SJQJ VARCHAR(8);--数据期间
 DECLARE @BBID VARCHAR(40);--报表id
 DECLARE @WDLIST VARCHAR(400);--WDLIST
 DECLARE @TOTALAMOUNT VARCHAR(50);
 DECLARE @FKDWID VARCHAR(40);--付款单位
 DECLARE @CP     VARCHAR(40);--产品
 DECLARE @COUNT NUMERIC(10);
 DECLARE @SQL VARCHAR(1000);

 DECLARE @OLDZT INT; --老状态
 DECLARE @NEWZT INT; --新状态

 SELECT @OLDZT=BBZT FROM DELETED;
 SELECT @NEWZT=BBZT FROM INSERTED;
 SELECT @WDLIST=WDLIST FROM INSERTED;

 IF (@OLDZT=@NEWZT) RETURN;

 SELECT @BBID=BBID FROM INSERTED;
 SELECT @BBBH=BBBH FROM NV_BB WHERE BBID=@BBID;
 
 IF @BBBH='SR-JK-03' BEGIN
  SELECT @VERSION=YSBBID FROM INSERTED;
  SELECT @SJQJ=SJQJ FROM NV_YSBB WHERE YSBBID=@VERSION;
  SET @SQL='DECLARE hts CURSOR  FOR SELECT JKZ.YSZBID,JKZ.WDEX4,JKZ.NUMVAL AS VAL,EX3.ZDID as CP FROM NV_DATA_'+@SJQJ+'A_WD_JKZ JKZ INNER JOIN SYS_EXWD3 EX3 ON EX3.ZDID=JKZ.WDEX3 INNER JOIN SYS_EXWD4 EX4 ON EX4.ZDID=JKZ.WDEX4 WHERE JKZ.YSZBID IN (''3A7CB1DF-EA2C-4DE0-A723-587C5D2B4EB6'',''6C76A12C-307A-4899-BF42-A3996F6B0816'') AND ''SJQJ|''+JKZ.SJQJID='''+@WDLIST+'''';
  EXEC(@SQL)
  EXEC('OPEN hts')
  FETCH NEXT FROM hts INTO @ZBID,@FKDWID,@TOTALAMOUNT,@CP--就是在断点刚跳的这里的时候,就退出了。
  WHILE (@@FETCH_STATUS=0) 
  BEGIN
   IF   @NEWZT=2 BEGIN
    SELECT @COUNT=COUNT(1) FROM TA_WS_BALANCE WHERE DATAPERIOD=@SJQJ AND YSZB=@ZBID AND FKDW=@FKDWID AND CP=@CP;
    IF @COUNT>0 BEGIN
     UPDATE TA_WS_BALANCE SET KFWS=KFWS+@TOTALAMOUNT,KYWS=KYWS+@TOTALAMOUNT WHERE DATAPERIOD=@SJQJ AND YSZB=@ZBID AND FKDW=@FKDWID AND CP=@CP;
    END;
    ELSE BEGIN
     INSERT INTO TA_WS_BALANCE(DATAPERIOD,YSZB,FKDW,KFWS,DJWS,KYWS,SFWS,CP) VALUES (@SJQJ,@ZBID,@FKDWID,@TOTALAMOUNT,0,@TOTALAMOUNT,0,@CP);
    END;
   END;
   IF (@OLDZT=2 AND @NEWZT=1) BEGIN
    UPDATE TA_WS_BALANCE SET KFWS=KFWS-@TOTALAMOUNT,KYWS=KYWS-@TOTALAMOUNT WHERE DATAPERIOD=@SJQJ AND YSZB=@ZBID AND FKDW=@FKDWID AND CP=@CP;
   END;
   FETCH NEXT FROM hts INTO @ZBID,@FKDWID,@TOTALAMOUNT,@CP
  END
  EXEC('CLOSE hts')
  EXEC('DEALLOCATE hts')
 END;  
END


 --测试代码

update SYS_YSBZBBZT  set bbzt=2 where ysbbid='4C0DFB73-1920-470D-A3C6-6E6B02AB389F'
and bbid='11091aad-8b13-4e27-826c-f9c075f2647a' and dwid='AD3C6869-C3EF-4097-8E5E-F34FCE02C9F2'
and wdlist='SJQJ|M01'

茹儿的主页 茹儿 | 菜鸟二级 | 园豆:203
提问于:2011-12-07 11:17
< >
分享
清除回答草稿
   您需要登录以后才能回答,未注册用户请先注册