How to write transactions in SQL Server stored procedures summary _mssql

The example in this article describes the method of writing transactions in SQL Server stored procedures. Share to everyone for your reference, specific as follows:

database transactions in SQL Server are quite useful, and since many SQL beginners write transactional code that is often flawed, this article introduces three different approaches, illustrating how to write the correct code in a stored procedure transaction. Hope to be able to help you.

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

BEGIN TRAN
UPDATE statement 1
... UPDATE Statement 2
... Delete Statement 3
... Commit Tran

The SQL written in this way is a big risk. Take a look at the following example:

CREATE TABLE demo (ID int not null)
go
begin tran
inserts into demo values (NULL)
inserts into demo values ( 2)
commit tran
go

An error message that violates the NOT NULL constraint occurs while executing, but then prompts (1 row (s) affected). After we performed the SELECT * from demo, we found that insert into demo values (2) was successfully executed. What's the reason? When a runtime error occurs in SQL Server, the default rollback the statement that caused the error, and the subsequent statement continues.

How to avoid such a problem? There are three different ways:

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. After each individual DML statement is executed, the execution state is judged immediately 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, the Try...catch exception handling mechanism is available.

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

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

I hope this article will help you with your SQL Server database program.