SQL Server：存儲過程中編寫事務處理的方法小結

/**8. SQLServer存儲過程中編寫事務處理的方法小結**/

原文出處： http://www.jb51.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