SQL Server：存储过程中编写事务处理的方法小结

潇洒紫焰

　　/**8. SQLServer存储过程中编写事务处理的方法小结**/
　　原文出处： http://www.iyunv.net/article/80636.htm
　　本文我们介绍了三种不同的方法，举例说明了如何在存储过程事务处理中编写正确的代码。
　　1. 常见写法：
　　在编写SQL Server 事务相关的存储过程代码时，经常看到下面这样的写法：
　　begin tran
　　update statement 1 ...
　　update statement 2 ...
　　delete statement 3 ...
　　commit tran
　　2. 存在的问题/隐患：
　　执行时会出现一个违反not null 约束的错误信息，但随后又提示(1 row(s) affected)。我们执行select* from demo 后发现insert into demo values(2) 却执行成功了。这是什么原因呢? 原来 SQL Server在发生runtime 错误时，默认会rollback引起错误的语句，而继续执行后续语句。
　　create table demo(id int notnull)
　　go
　　begin tran
　　insert into demo values (null)
　　insert into demo values (2)
　　commit tran
　　go
　　3. 如何避免这样的问题呢？有三种方法：
　　方法1. 在事务语句最前面加上set xact_abort on；当xact_abort 选项为on 时，SQLServer在遇到错误时会终止执行并rollback 整个事务。
　　setxact_abort on
　　begintran
　　updatestatement 1 ...
　　updatestatement 2 ...
　　deletestatement 3 ...
　　committran
　　go
　　方法2. 在每个单独的DML语句执行后，立即判断执行状态，并做相应处理。
　　begintran
　　updatestatement 1 ...
　　if @@error 0
　　beginrollbacktran
　　gotolabend
　　end
　　deletestatement 2 ...
　　if @@error  0
　　beginrollbacktran
　　gotolabend
　　end
　　committran
　　labend:
　　go
　　方法3. 在SQL Server 2005中，可利用 try...catch 异常处理机制。
　　begintran
　　begintry
　　updatestatement 1 ...
　　deletestatement 2 ...
　　endtry
　　begincatch
　　if @@trancount >0
　　rollbacktran
　　endcatch
　　if @@trancount >0
　　committran
　　go
　　4. 演示：下面是个简单的存储过程，演示事务处理过程。
　　--set nocount on 表示不返回计数
　　create procedure dbo.pr_tran_inprocas begin set nocount on
　　begin tran
　　update statement 1...
　　if @@error 0
　　begin rollback tran
　　return -1 end
　　delete statement 2...
　　if @@error 0
　　begin rollback tran
　　return -1
　　end commit tran
　　return 0
　　end
　　go
　　执行：
　　Exec dbo.pr_tran_inproc