怎么才能使这个存储过程具有原子性

coordinate 2004-03-05 04:41:16
为什么下面我写的存储过程在执行的过程中失败的时候,失败之前的修改还被提交给系统,我用了set xact_abort on。怎么样做才能才能在这个存储过程失败的时候,将所有之前的操作撤消。
if exists(select name from sysobjects where name='init_wage' and type='p')
drop procedure init_wage
go
create procedure init_wage @wage_date smalldatetime,@wtemplate int
as
set xact_abort on
declare @max_num int,@serial int,@wid int
declare @bwage money
set @max_num=(select max(wage_num) from emp_wage)
if @max_num is null set @max_num=0
declare c1 cursor for
select serial,basic_wage from basic where state=1
open c1
fetch next from c1 into @serial,@bwage
while @@fetch_status=0
begin
set @max_num=@max_num+1
if @bwage is null set @bwage=0
insert into emp_wage(wage_num,wage_date,wage_basic,wage_ought,wage_get)
values(@max_num,@wage_date,@bwage,@bwage,@bwage)
declare c2 cursor for select w_id from template where t_id=@wtemplate
open c2
fetch next from c2 into @wid
while @@fetch_status=0
begin
insert into wage(wage_num,item,amount) values(@max_num,@wid,0)
fetch next from c2 into @wid
end
close c2
deallocate c2
fetch next from c1 into @serial,@bwage
end
close c1
deallocate c1
...全文
52 点赞 收藏 2
写回复
2 条回复
切换为时间正序
当前发帖距今超过3年,不再开放新的回复
发表回复

还没有回复,快来抢沙发~

相关推荐
发帖
MS-SQL Server
创建于2007-09-28

3.3w+

社区成员

MS-SQL Server相关内容讨论专区
申请成为版主
帖子事件
创建了帖子
2004-03-05 04:41
社区公告
暂无公告