在编写SQL Server 事务相关的存储过程代码时,经常看到下面这样的写法:
begintran
updatestatement1...
updatestatement2...
deletestatement3...
committran
这样编写的SQL存在很大隐患。请看下面的例子:
createtabledemo(idintnotnull)
go
begintran
insertintodemovalues(null)
insertintodemovalues(2)
committran
go
执行时会出现一个违反not null 约束的错误信息,但随后又提示(1 row(s) affected)。 我们执行select * from demo 后发现insert into demo values(2) 却执行成功了。 这是什么原因呢? 原来 SQL Server在发生runtime 错误时,默认会rollback引起错误的语句,而继续执行后续语句。
1. 在事务语句最前面加上set xact_abort on
setxact_aborton
begintran
updatestatement1...
updatestatement2...
deletestatement3...
committran
go
当xact_abort 选项为on 时,SQL Server在遇到错误时会终止执行并rollback 整个事务。 这个意思是说,如果该事务中包含子事务,子事务也会回滚。
2. 在每个单独的DML语句执行后,立即判断执行状态,并做相应处理。
begintran
updatestatement1...
if@@error<>0
beginrollbacktran
gotolabend
end
deletestatement2...
if@@error<>0
beginrollbacktran
gotolabend
end
committran
labend:
go
3. 在SQL Server 中,可利用 try...catch 异常处理机制。
begintran
begintry
updatestatement1...
deletestatement2...
endtry
begincatch
if@@trancount>0
rollbacktran
endcatch
if@@trancount>0
committran
go
下面是个简单的存储过程,演示事务处理过程。
createproceduredbo.pr_tran_inprocasbeginsetnocounton
begintran
updatestatement1...
if@@error<>0
beginrollbacktran
return-1end
deletestatement2...
if@@error<>0
beginrollbacktran
return-1
endcommittran
return0
end
go
原文出处:/healingangle/article/details/24999175