请大家看看我下面这个存储过程,我是疑问是对多表操作都要用事务来处理吗?事务用多了会不会不好?我这个存储过程是否合理,大家是怎样写的?
——————————————————————————
/*
作用:赎回基金
时间:2008.06.06
*/
ALTER PROCEDURE AA_sp_FundRedeem
(
@CombId int,--加入的组合编号
@JCode varchar(10), --基金编码
@FundValue float,--基金净值
@FundLots float,--赎回的基金份额
@DevotionMoney float, --交易金额
@effDate datetime, --生效日
@Notes varchar(200), --备注
@ret tinyint OutPut --返回执行结果 0执行成功 1执行失败
)
AS begin tran declare @err1 int ,@err2 int,@err3 int,@err4 int,@err5 int,@TempId int,@TempId2 int
/*
******************************************************************* 操作1:删除基金盈亏表(BM_FCombWinTbl)的购买时间以后的所有记录。 *******************************************************************
*/
exec AA_sp_FundDeleteAfterTimeWinMsg @CombId,@JCode,@effDate
set @err1=@@error /*
******************************************************************* 操作2:添加 基金组合明细表数据 *******************************************************************
*/
declare @FundName varchar(100) --下一个DetailId编号
select @TempId=ISNULL(max(DetailId)+1,1) from BM_FCombDetailTbl
select @FundName=Title from BM_Fund where JCode=@JCode
insert into BM_FCombDetailTbl(DetailId,CombId,JCode,FundName,Notes) values(@TempId,@CombId,@JCode,@FundName,@Notes)
set @err2=@@error /*
******************************************************************* 操作2:添加 基金操作表数据 *******************************************************************
*/ --汇总数据(注意:应取该操作时间的上一个操作时间的数据)
declare @TFundLots float,@TFundVal float,@TDevotionMoney float,@TTakeMoney float,@TBonus float
if object_id('tempdb..#t1') is not null
drop table #t1
select a.*,b.CombId,b.JCode,b.FundName into #t1 from BM_FCombOpertTbl a inner join BM_FCombDetailTbl b on
a.DetailId=b.DetailId where b.JCode=@JCode and b.CombId =@CombId select top 1 @TFundLots=TFundLots,@TFundVal=TFundVal,@TDevotionMoney=TDevotionMoney,@TTakeMoney=TTakeMoney,@TBonus=TBonus
from #t1 where OperDate =(select top 1 OperDate from #t1 where OperDate<=@effDate order by OperDate desc,[ID] desc)
order by OperDate desc,[ID] desc --获取基金当前净值
declare @dNetVal float,@pNetVal float
SELECT @dNetVal=NetVal FROM BM_F_NetVTbl WHERE (JCode=@JCode and dDate=dbo.AA_fun_GetDate()) --获取上一交易日净值
SELECT @pNetVal=NetVal FROM BM_F_NetVTbl WHERE (JCode=@JCode and dDate=dbo.AA_fun_GetPreDate(dbo.AA_fun_GetDate()))
--最新汇总基金份额
set @TFundLots=@TFundLots-@FundLots --最新汇总基金市值
set @TFundVal=@TFundLots*@dNetVal --最新汇总投入金额
set @TDevotionMoney=@TDevotionMoney --最新汇总收回金额
set @TTakeMoney=@TTakeMoney+@DevotionMoney --最新汇总现金红利
set @TBonus=@TBonus
select @TempId2=max(DetailId) from BM_FCombDetailTbl --获取刚才添加的最大记录数
select @TempId=ISNULL(max([Id])+1,1) from BM_FCombOpertTbl --基金操作表最大编号
insert into BM_FCombOpertTbl([ID],DetailId,OperType,OperDate,FundLots,FundVal,DevotionMoney,TakeMoney,Bonus,TFundLots,
TFundVal,TDevotionMoney,TTakeMoney,TBonus,DayWin,FloatWin,YieldPer)
values(@TempId,@TempId2,101103,@effDate,@FundLots,-3.402823E+38,-3.402823E+38,@DevotionMoney,-3.402823E+38,@TFundLots,
@TFundVal,@TDevotionMoney,@TTakeMoney,@TBonus,-3.402823E+38,-3.402823E+38,-3.402823E+38)
set @err3=@@error /*
******************************************************************* 操作3:调用存储过程重新计算该操作之后的记录汇总数据并添加基金盈亏表数据 *******************************************************************
*/
exec AA_sp_FundUpdateEachMsg @JCode,@CombId,@effDate,@FundLots,@DevotionMoney,101103,@TempId
set @err4=@@error
if(@err1=0 and @err2=0 and @err3=0 and @err4=0)
begin
commit tran
set @ret=0
end
else
begin
rollback tran
set @ret=1
end
GO
SET QUOTED_IDENTIFIER OFF
GO
SET ANSI_NULLS ON
GO
——————————————————————————
/*
作用:赎回基金
时间:2008.06.06
*/
ALTER PROCEDURE AA_sp_FundRedeem
(
@CombId int,--加入的组合编号
@JCode varchar(10), --基金编码
@FundValue float,--基金净值
@FundLots float,--赎回的基金份额
@DevotionMoney float, --交易金额
@effDate datetime, --生效日
@Notes varchar(200), --备注
@ret tinyint OutPut --返回执行结果 0执行成功 1执行失败
)
AS begin tran declare @err1 int ,@err2 int,@err3 int,@err4 int,@err5 int,@TempId int,@TempId2 int
/*
******************************************************************* 操作1:删除基金盈亏表(BM_FCombWinTbl)的购买时间以后的所有记录。 *******************************************************************
*/
exec AA_sp_FundDeleteAfterTimeWinMsg @CombId,@JCode,@effDate
set @err1=@@error /*
******************************************************************* 操作2:添加 基金组合明细表数据 *******************************************************************
*/
declare @FundName varchar(100) --下一个DetailId编号
select @TempId=ISNULL(max(DetailId)+1,1) from BM_FCombDetailTbl
select @FundName=Title from BM_Fund where JCode=@JCode
insert into BM_FCombDetailTbl(DetailId,CombId,JCode,FundName,Notes) values(@TempId,@CombId,@JCode,@FundName,@Notes)
set @err2=@@error /*
******************************************************************* 操作2:添加 基金操作表数据 *******************************************************************
*/ --汇总数据(注意:应取该操作时间的上一个操作时间的数据)
declare @TFundLots float,@TFundVal float,@TDevotionMoney float,@TTakeMoney float,@TBonus float
if object_id('tempdb..#t1') is not null
drop table #t1
select a.*,b.CombId,b.JCode,b.FundName into #t1 from BM_FCombOpertTbl a inner join BM_FCombDetailTbl b on
a.DetailId=b.DetailId where b.JCode=@JCode and b.CombId =@CombId select top 1 @TFundLots=TFundLots,@TFundVal=TFundVal,@TDevotionMoney=TDevotionMoney,@TTakeMoney=TTakeMoney,@TBonus=TBonus
from #t1 where OperDate =(select top 1 OperDate from #t1 where OperDate<=@effDate order by OperDate desc,[ID] desc)
order by OperDate desc,[ID] desc --获取基金当前净值
declare @dNetVal float,@pNetVal float
SELECT @dNetVal=NetVal FROM BM_F_NetVTbl WHERE (JCode=@JCode and dDate=dbo.AA_fun_GetDate()) --获取上一交易日净值
SELECT @pNetVal=NetVal FROM BM_F_NetVTbl WHERE (JCode=@JCode and dDate=dbo.AA_fun_GetPreDate(dbo.AA_fun_GetDate()))
--最新汇总基金份额
set @TFundLots=@TFundLots-@FundLots --最新汇总基金市值
set @TFundVal=@TFundLots*@dNetVal --最新汇总投入金额
set @TDevotionMoney=@TDevotionMoney --最新汇总收回金额
set @TTakeMoney=@TTakeMoney+@DevotionMoney --最新汇总现金红利
set @TBonus=@TBonus
select @TempId2=max(DetailId) from BM_FCombDetailTbl --获取刚才添加的最大记录数
select @TempId=ISNULL(max([Id])+1,1) from BM_FCombOpertTbl --基金操作表最大编号
insert into BM_FCombOpertTbl([ID],DetailId,OperType,OperDate,FundLots,FundVal,DevotionMoney,TakeMoney,Bonus,TFundLots,
TFundVal,TDevotionMoney,TTakeMoney,TBonus,DayWin,FloatWin,YieldPer)
values(@TempId,@TempId2,101103,@effDate,@FundLots,-3.402823E+38,-3.402823E+38,@DevotionMoney,-3.402823E+38,@TFundLots,
@TFundVal,@TDevotionMoney,@TTakeMoney,@TBonus,-3.402823E+38,-3.402823E+38,-3.402823E+38)
set @err3=@@error /*
******************************************************************* 操作3:调用存储过程重新计算该操作之后的记录汇总数据并添加基金盈亏表数据 *******************************************************************
*/
exec AA_sp_FundUpdateEachMsg @JCode,@CombId,@effDate,@FundLots,@DevotionMoney,101103,@TempId
set @err4=@@error
if(@err1=0 and @err2=0 and @err3=0 and @err4=0)
begin
commit tran
set @ret=0
end
else
begin
rollback tran
set @ret=1
end
GO
SET QUOTED_IDENTIFIER OFF
GO
SET ANSI_NULLS ON
GO
create proc sp_test(@value1 varchar(50))
as
begin transaction
insert tt(name) select @value1
if @@error <> 0
begin
Rollback
end
insert tttt(ID) select @value1
if @@error <> 0
begin
Rollback
end
commit
exec sp_test 'b'
跨服务器不支持事务为了保证数据的一致性,可以用sql脚本检验数据库的一致性