Shulou(Shulou.com)06/01 Report--

/** 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

Welcome to subscribe "Shulou Technology Information " to get latest news, interesting things and hot topics in the IT industry, and controls the hottest and latest Internet news, technology news and IT industry trends.