SQL Server: a summary of the methods of writing transactions in stored procedures
/** 8. Summary of ways to write transactions in SQL Server stored procedures **/
Source: http://www.jb51.net/article/80636.htm
In this article, we introduced three different approaches, illustrating how to write the right code in stored procedure transactions.
1. Common Writing:
When writing SQL Server transaction-related stored procedure code, you often see something like this:
begin tran
update statement 1 ...
update statement 2 ...
delete statement 3 ...
commit tran
2. Problems/hidden dangers:
Execution results in an error message that violates the not null constraint, followed by (1 row(s) affected). After executing select* from demo, we find that insert into demo values(2) succeeds. What is the reason for this? When SQL Server runs a runtime error, it rolls back the statement that caused the error by default, and continues to execute subsequent statements.
create table demo(id int notnull)
go
begin tran
insert into demo values (null)
insert into demo values (2)
commit tran
go
3. How can we avoid such problems? There are three ways:
methods 1. Prefix the transaction statement with set xact_abort on; when the xact_abort option is on, SQL Server terminates execution and rolls back the entire transaction when it encounters an error.
setxact_abort on
begintran
updatestatement 1 ...
updatestatement 2 ...
deletestatement 3 ...
committran
go
Method 2. After each individual DML statement is executed, the execution status is immediately judged and processed accordingly.
begintran
updatestatement 1 ...
if @@error 0
beginrollbacktran
gotolabend
end
deletestatement 2 ...
if @@error 0
beginrollbacktran
gotolabend
end
committran
labend:
go
Method 3. In SQL Server 2005, try... catch exception handling mechanism.
begintran
begintry
updatestatement 1 ...
deletestatement 2 ...
endtry
begincatch
if @@trancount >0
rollbacktran
endcatch
if @@trancount >0
committran
go
4. Demo: The following is a simple stored procedure that demonstrates transaction processing.
--set nocount on means not to return count
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
Implementation:
Exec dbo.pr_tran_inproc
