参考：sql事务（Transaction）用法介绍及回滚实例
-- 测试表
create table T1
(
ID int identity(1,1),
Code nvarchar(10)
)
Go
-- 存储过程
create procedure p_RollbackTest
as
begin
set nocount on
declare @n int = 0
begin tran outTrans
insert into T1(Code) values('A') -- 保存入表中
begin try
begin tran innerTrans
insert into T1(Code) values('B') -- savepoint之前，保存入表中
save tran savepoint -- 创建事务保存点
insert into T1(Code) values('C') -- savepoint之后，丢弃
set @n = 10 / 0 -- 异常
commit tran innerTrans
end try
begin catch
rollback tran savepoint --回滚到保存点
commit tran innerTrans -- 注意：这里用rollback tran会出错，只能用commit tran
end catch
insert into T1(Code) values('D') -- 保存入表中
commit tran outTrans
end
Go
Exec p_RollbackTest
Go
select * from T1
Go 