function TDm_Mzsf.JxSaveMzSf_zf(FirstFp, hLastFph:longint):Boolean;
var//RCount 为从0开始的 ,即为记录-1//RCount为从0开始的 ,即为记录-1
I:integer; //临时变量
RCount,temp:Longint;
Zzs:Longint; //系统参数值,需要的总张数,当前发票基号
begin
DMCommon.JzDatabase.StartTransaction;
try
RCount :=hLastFph-FirstFp;
JxCheckRcount(RCount); //判断本次收费的纪录数是否为0
JxCheckFph(InttoStr(FirstFp));//判断jxmzsflist中是否有本次收费的首发票号
Zzs:= RCount+1; //总张数
temp :=FirstFp;
while temp<=hLastFph do
begin
with MzSf_Insert do
begin
ParamByName('@code_Sp').AsString:=Trim(Code_Sp_Zf);
ParamByName('@CName_Sp').AsString:=Trim(DsCname_Zf);
ParamByName('@JE').AsCurrency:=Je_Zf;
ParamByName('@FPH').AsString:=IntToStr(temp);
ParamByName('@FPH2').AsString:=Fph2_Zf;
ParamByName('@Fph2_hj').AsInteger:=Fph2_Hj_Zf;
ParamByName('@code_sffl').AsString:=Trim(code_SfFl_Zf);
ParamByName('@code_sfjc').AsString:=Trim(code_SfJc_Zf);
ParamByName('@ysje').AsCurrency:=YsJe_Zf;
ParamByName('@ssje').AsCurrency:=SsJe_Zf;
ParamByName('@code_kjkm').AsString:=Trim(Code_KjKm_Zf);
ParamByName('@code_Tjxm').AsString:=Trim(code_tjxm_zf); ParamByName('@code_Br').AsString:=Trim(Code_Br_Zf);
ParamByName('@cname_Br').AsString:=Trim(Cname_Br_Zf);
ParamByName('@code_cf').AsString:='';
ParamByName('@code_ys').AsString:='';
ParamByName('@code_bmks').AsString:='';
ParamByName('@code_bm').AsString:='';
ParamByName('@code_pj').AsString:='';
ParamByName('@time_pj').AsDateTime:=0;
ParamByName('@code_sf').AsString:=GrCs.UserCode;
ParamByName('@time_sf').AsDateTime:=DMCommon.GetServerDate_wy;
ParamByName('@code_fy').AsString:='';
ParamByName('@time_fy').AsDateTime:=0;
ParamByName('@code_sh').AsString:='';
ParamByName('@time_sh').AsDateTime:=0;
ParamByName('@code_SfBmKs').AsString:='';
ParamByName('@code_op').AsString:=GrCs.UserCode;
ParamByName('@jzid').AsInteger:=0;
try
Prepare;
ExecProc;
UnPrepare;
except
Proc_Debug('存储过程MzSf_Insert出错!');
end;
end;
Inc(temp);
end;
with QSerch do
begin
close;
sql.Clear;
sql.Add('update JxMzZyFpb ');
sql.Add('set Fp_Use=Fp_Use+:zs ');
sql.Add('where CAST (fph_First AS bigint)<=:fph ');
sql.Add(' and CAST (Fph_Last AS bigint)>=:fph ');
sql.Add(' and code_fplb=:code_fplb ');
sql.Add(' and code_lyjc=:lyJc ');
ParamByName('lyJc').AsString :=const_FpLyjc_Ly;
ParamByName('code_fplb').asstring := const_fplb_mz;
ParamByName('fph').AsInteger := FirstFp;
ParamByName('zs').AsInteger := Zzs;
try Execsql;Result :=True;
except Proc_Debug(Sql.Text);Result :=False; end;
DMCommon.JzDatabase.Commit;
Result :=True;
except
DMCommon.JzDatabase.Rollback;
Result:=False;
Proc_ShowMessage('保存不成功!请重试...');
end;
end;这是整个事务处理的过程,当存储过程保存不成功时,为什么不回滚QSerch 所发生的数据,请问用什么方法解决?
var//RCount 为从0开始的 ,即为记录-1//RCount为从0开始的 ,即为记录-1
I:integer; //临时变量
RCount,temp:Longint;
Zzs:Longint; //系统参数值,需要的总张数,当前发票基号
begin
DMCommon.JzDatabase.StartTransaction;
try
RCount :=hLastFph-FirstFp;
JxCheckRcount(RCount); //判断本次收费的纪录数是否为0
JxCheckFph(InttoStr(FirstFp));//判断jxmzsflist中是否有本次收费的首发票号
Zzs:= RCount+1; //总张数
temp :=FirstFp;
while temp<=hLastFph do
begin
with MzSf_Insert do
begin
ParamByName('@code_Sp').AsString:=Trim(Code_Sp_Zf);
ParamByName('@CName_Sp').AsString:=Trim(DsCname_Zf);
ParamByName('@JE').AsCurrency:=Je_Zf;
ParamByName('@FPH').AsString:=IntToStr(temp);
ParamByName('@FPH2').AsString:=Fph2_Zf;
ParamByName('@Fph2_hj').AsInteger:=Fph2_Hj_Zf;
ParamByName('@code_sffl').AsString:=Trim(code_SfFl_Zf);
ParamByName('@code_sfjc').AsString:=Trim(code_SfJc_Zf);
ParamByName('@ysje').AsCurrency:=YsJe_Zf;
ParamByName('@ssje').AsCurrency:=SsJe_Zf;
ParamByName('@code_kjkm').AsString:=Trim(Code_KjKm_Zf);
ParamByName('@code_Tjxm').AsString:=Trim(code_tjxm_zf); ParamByName('@code_Br').AsString:=Trim(Code_Br_Zf);
ParamByName('@cname_Br').AsString:=Trim(Cname_Br_Zf);
ParamByName('@code_cf').AsString:='';
ParamByName('@code_ys').AsString:='';
ParamByName('@code_bmks').AsString:='';
ParamByName('@code_bm').AsString:='';
ParamByName('@code_pj').AsString:='';
ParamByName('@time_pj').AsDateTime:=0;
ParamByName('@code_sf').AsString:=GrCs.UserCode;
ParamByName('@time_sf').AsDateTime:=DMCommon.GetServerDate_wy;
ParamByName('@code_fy').AsString:='';
ParamByName('@time_fy').AsDateTime:=0;
ParamByName('@code_sh').AsString:='';
ParamByName('@time_sh').AsDateTime:=0;
ParamByName('@code_SfBmKs').AsString:='';
ParamByName('@code_op').AsString:=GrCs.UserCode;
ParamByName('@jzid').AsInteger:=0;
try
Prepare;
ExecProc;
UnPrepare;
except
Proc_Debug('存储过程MzSf_Insert出错!');
end;
end;
Inc(temp);
end;
with QSerch do
begin
close;
sql.Clear;
sql.Add('update JxMzZyFpb ');
sql.Add('set Fp_Use=Fp_Use+:zs ');
sql.Add('where CAST (fph_First AS bigint)<=:fph ');
sql.Add(' and CAST (Fph_Last AS bigint)>=:fph ');
sql.Add(' and code_fplb=:code_fplb ');
sql.Add(' and code_lyjc=:lyJc ');
ParamByName('lyJc').AsString :=const_FpLyjc_Ly;
ParamByName('code_fplb').asstring := const_fplb_mz;
ParamByName('fph').AsInteger := FirstFp;
ParamByName('zs').AsInteger := Zzs;
try Execsql;Result :=True;
except Proc_Debug(Sql.Text);Result :=False; end;
DMCommon.JzDatabase.Commit;
Result :=True;
except
DMCommon.JzDatabase.Rollback;
Result:=False;
Proc_ShowMessage('保存不成功!请重试...');
end;
end;这是整个事务处理的过程,当存储过程保存不成功时,为什么不回滚QSerch 所发生的数据,请问用什么方法解决?
增加一参数:@QSerch
然后把QSerch的sql语句传进去执行就可以了
ParamByName('@QSerch').AsString:=sqlstr; 在过程里执行是用
exec @QSerch
.....执行后面的语句
-----------------配送状态录入表
CREATE PROCEDURE TT_P017@MainBiao Varchar(20), ---是否插入主表记录
@PeopleId varchar(20), --调度人员号
@Groupname varchar(20), --配送组织
@SendMainId varchar(7000) ,-- 送货细单号
@SendMinuteId varchar(7000) ,-- 送货细单号
@GoodsCount varchar(7000), ---送货数量
@ShouldGet varchar(7000), ----应收
@ShouldPay varchar(7000), -----应付
@count int,
@re_String varchar(2) out,
@re_message varchar(100) out,
@re_Sid varchar(14) out
as
declare
@n int,
@StateMainId Varchar(30) , --调度单号
@Smainid varchar(30),
@Sminuteid varchar(30),
@StateMinuteId varchar(30),
@GroupID varchar(20),
@gCount money,
@Get money,
@Pay money,
@day varchar(10),
@RiQi varchar(10)
set @day=(select (convert(varchar(10), GETDATE())) )
set @riqi=(substring(@day,7,10)+'-' +substring(@day,1,2)+'-'+substring(@day,4,5) )
------------------
set @StateMinuteId=((select isnull(max(StateMinuteid),'1000000000') from TT_StateMinute) +1 )
set @StateMainId=((select isnull(max(StateMainid),'1000000000') from TT_statemain) +1 ) ------添加状态主表begin tran
set set nocount on
begin
set @GroupId=(select group_Id From TT_SUBGROUP where Group_Name=@GroupName)
Insert Into TT_StateMain values(@StateMainID,@PeopleID,@RiQi,@GroupId)
if @@error <>0 goto error1
end
--------添加状态细单
begin
set @n=1
while @n <=@count
begin
select @Gcount= convert(money, dbo.gain(@goodscount,@n) ) ,@SMainID=convert (varchar(20), dbo.gain(@SendMainID,@n) ) ,@SMinuteId=dbo.gain(@SendMinuteId,@n) , @get=convert( money,dbo.gain (@ShouldGet,@n) ), @pay=convert(money,dbo.gain(@shouldPay,@n) )
Insert Into TT_StateMinute Values(@StateMinuteId,@StateMainid,@SMainID,@SMinuteId,@GCount,@Get,@Pay)
if @@error <>0 goto error2
else
goto succ update TT_SendMinute set Sendstate ='02' where SendMinuteid=@SendMinuteiD-----state
if @@error <>0 goto error3
set @n =@n +1
end
end
goto succ
------------------
succ:
begin set @re_message='1000001插入状态主表成功'
commit transaction
goto pover
enderror1:
begin
rollback tran
set @re_message ='1000002插入状态主表失败'
goto pover
end
error2:
begin
RollBack Tran
set @re_message ='1000003插入状态细表失败'
goto pover
endError3:
begin
rollBack Tran
set @re_message ='1000004更新失败 '
goto pover
end
pover:
begin
set nocount off
end
GO