SET XACT_ABORT ON
BEGIN TRAN
SELECT @G_1=(SELECT SUBSTRING(DQZ_C,1,1) FROM CSSZB_S WHERE ID_I=0)
SELECT @ckbj_C =ckbj_C,@ywfs_C=ywfs_C,@pjxsfs_C=pjxsfs_C FROM PJxscK_H WHERE [email protected]_C
SELECT @FLAGE= '0 '
DECLARE CURDETAIL CURSOR FOR SELECT ID_I,ZID_C,ISNULL(CW_C, ' ') as cw_c,jJ_F,SHL_I,tkqsj_f,DHBJ_C FROM PJcKMX_B WHERE [email protected]_C
OPEN CURDETAIL
FETCH NEXT FROM CURDETAIL INTO @ID_I,@ZID_C,@CW_C,@JJ_F,@SHL_I,@tkqsj_f,@DHBJ_C
WHILE (@@FETCH_STATUS = 0)
BEGI
IF (EXISTS(SELECT * FROM PJKC_R WHERE [email protected]_C AND CW_C [email protected]_C ))
BEGIN
select @jqKC_I=( SELECT sum(kc_I) FROM PJKC_R WHERE [email protected]_C AND CW_C [email protected]_C )
UPDATE PJKC_R SET KC_I=KC_I+(-1)[email protected][email protected]_I WHERE [email protected]_C ND CW_C [email protected]_C
select @jhKC_I=( SELECT sum(kc_I) FROM PJKC_R WHERE [email protected]_C AND CW_C [email protected]_C )
● insert into [out](id_C,rq_D,zid_C,qkc_f, hkc_f,ck_F,jj_F,cw_C,fx)values (@id_C,getdate(),@ZID_C,@jqKC_I,@jhKC_I,@SHL_I,@jj_F,@cw_C,@fx)
END
ELSE begin
● insert into [out](id_C,rq_D,zid_C, qkc_f, hkc_f,ck_F,jj_F,cw_C,fx)values (@id_C,getdate(),@ZID_C,-9999,@[email protected]_I,@SHL_I,@jj_F,@cw_C,@fx)
if @pjxsfs_C= '客户退还 '
update pjckmx_bt set ktksh_i=ktksh_i-(-1)[email protected][email protected]_I WHERE ID_i=CONVERT(int,@tkqsj_f)
FETCH NEXT FROM CURDETAIL INTO @ID_I,@ZID_C,@CW_C,@JJ_F,@SHL_I ,@tkqsj_f,@DHBJ_C
END
CLOSE CURDETAIL
DEALLOCATE CURDETAIL
COMMIT TRAN
SET XACT_ABORT OFF
上面带●执行后的结果为:
4382 E0006095 2007-04-21 13:48:46.497 1005080-JAA1Z 1.0 0.0 1.0
4383 E0006095 2007-04-21 13:48:46.513 1005212-JA 8.0 7.0 1.0
4387 E0006095 2007-04-21 13:55:01.577 1005080-JAA1Z 0.0 1.0 1.0
4388 E0006095 2007-04-21 13:55:01.577 1005212-JA 7.0 8.0 1.0
4389 E0006095 2007-04-21 13:55:16.153 1005080-JAA1Z 1.0 0.0 1.0
4390 E0006095 2007-04-21 13:55:16.153 1005212-JA 8.0 7.0 1.0
4518 E0006095 2007-04-21 18:58:40.170 1005080-JAA1Z 0.0 1.0 1.0