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

[coordinate]

为什么下面我写的存储过程在执行的过程中失败的时候，失败之前的修改还被提交给系统，我用了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