Use of SQL transactions

database transaction processing in SQL Server is an important concept, but also a little bit difficult to understand, many SQL beginners write transaction processing code is often a vulnerability, this article describes three different methods, examples of how to write the correct code in the stored procedure transaction.

When 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

There is a great danger in writing SQL. Take a look at the following example:

   CREATE TABLE demo (ID int not null)   go to   begin tran      INSERT INTO demo values (NULL) INSERT INTO      demo values (2 )   Commit Tran   go

There is an error message that violates the NOT NULL constraint when executing, but then prompts (1 row (s) affected). After we executed the SELECT * from demo, we found that insert into demo values (2) was executed successfully. What is the reason for this? It turns out that SQL Server, when a runtime error occurs, rollback the statement that caused the error by default and continues with the subsequent statement.

How to avoid this problem? There are three ways to do this:

1. Precede the transaction statement with SET XACT_ABORT on

   Set XACT_ABORT on   BEGIN TRAN      UPDATE statement 1      ... UPDATE Statement 2      ... Delete Statement 3   ... Commit Tran   Go

When the Xact_abort option is on, SQL Server terminates execution and rollback the entire transaction when an error is encountered.

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

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   ... End Try   begin catch      if @ @trancount > 0         rollback tran   end catch   if @ @trancount > 0      Commit t Ran   go

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

   CREATE PROCEDURE Dbo.pr_tran_inproc   as   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

Use of SQL transactions