set xact_abort on --当出错全部回滚
begain transactiondeclare @intErrorSum intdeclare @tablename varchar(200)
declare @strSQL1 varchar(1000)
set @strSQL1='update '+@tablename+' set name=''zhu'''
exec (@strSQL1)
set @intErrorSum=@intErrorSum+@@errordeclare @strSQL2 varchar(1000)
set @strSQL2='insert into '+@tablename+'(xh) values(20)'
exec (@strSQL2)
set @intErrorSum=@intErrorSum+@@errorif @intErrorSum>0 --错误就回滚
rollback transaction
else --否则释放事务
commit transaction
begain transactiondeclare @intErrorSum intdeclare @tablename varchar(200)
declare @strSQL1 varchar(1000)
set @strSQL1='update '+@tablename+' set name=''zhu'''
exec (@strSQL1)
set @intErrorSum=@intErrorSum+@@errordeclare @strSQL2 varchar(1000)
set @strSQL2='insert into '+@tablename+'(xh) values(20)'
exec (@strSQL2)
set @intErrorSum=@intErrorSum+@@errorif @intErrorSum>0 --错误就回滚
rollback transaction
else --否则释放事务
commit transaction
gocreate table #Table1 (a tinyint)
go
begin tran
insert #table1 values(1) ----成功
exec('insert #table1 values(1000)') ----这句将报错
commit tran
go
select * from #table1
go
drop table #table1