SQL Server: Summary of methods for writing transactions in stored procedures

/**8. A summary of methods for writing transactions in SQL Server stored procedures **/

Original Source: http://www.jb51.net/article/80636.htm

In this article, we present three different methods, illustrating how to write the correct code in a stored procedure transaction . .

1. Common wording:

in writing SQL Server transaction-related stored procedure Code, you often see the following notation :

BEGIN Tran

UPDATE Statement 1 ...

UPDATE Statement 2 ...

Delete Statement 3 ...

Commit Tran

2. Existing problems / pitfalls:

execution occurs when a violationNot nullthe error message for the constraint, but then prompts(1 row (s) affected). We performselect* from Demolater foundINSERT INTO demo values (2)but the execution was successful. What's the reason?? turns outSQL Serverin the eventRuntimeerror, the default isrollbackthe statement that caused the error, and the continuation of the subsequent statement.

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 I avoid such a problem? There are three ways to do this :

Method1.at the front of the transaction statement, addset Xact_abort on; whenXact_abortoption is onwhen theSQL Serverwhen an error is encountered, execution is terminated androllbackthe entire transaction.

Set Xact_abort on

begin Tran

Update Statement 1 ...

Update Statement 2 ...

Delete Statement 3 ...

Commit Tran

Go

Method 2. once each individual DML statement executes, the execution status is immediately judged and handled accordingly.

begin Tran

Update Statement 1 ...

If @ @error <>0

begin rollback Tran

Goto Labend

End

Delete Statement 2 ...

If @ @error <> 0

begin rollback Tran

Goto Labend

End

Commit Tran

Labend:

Go

Method 3. in SQL Server 2005 , you can take advantage of the try...catch exception handling mechanism .

begin Tran

begin Try

Update Statement 1 ...

Delete Statement 2 ...

Endtry

begin Catch

If @ @trancount >0

rollback Tran

End Catch

If @ @trancount >0

Commit Tran

Go

4. Demo: The following is a simple stored procedure that demonstrates the transaction process .

--set nocount on means no count is returned

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

Perform:

Exec Dbo.pr_tran_inproc

This article comes from the "Ricky's blog" blog, please be sure to keep this source http://57388.blog.51cto.com/47388/1772724

SQL Server: Summary of methods for writing transactions in stored procedures