Summary of writing transaction processing methods in SQL Server Stored Procedures, and storing process transactions

Summary of writing transaction processing methods in SQL Server Stored Procedures, and storing process transactions

This article describes how to write transactions in the SQL Server stored procedure. We will share this with you for your reference. The details are as follows:

In SQL Server, Database Transaction processing is quite useful. In view of the fact that many SQL beginners often have vulnerabilities in the transaction processing code, we have introduced three different methods, the example shows how to write the correct code in the Stored Procedure transaction processing. Hope to help you.

When writing SQL Server transaction-related Stored Procedure Code, we often see the following statement:

begin tranupdate statement 1 ...update statement 2 ...delete statement 3 ...commit tran

This SQL statement poses a major risk. See the following example:

create table demo(id int not null)gobegin traninsert into demo values (null)insert into demo values (2)commit trango

During execution, an error message that violates the not null constraint is displayed, but the prompt is (1 row (s) affected ). After executing select * from demo, we found that insert into demo values (2) was successfully executed. Why? In the past, when a runtime error occurs on SQL Server, rollback will cause the wrong statement by default, and continue to execute subsequent statements.

How can this problem be avoided? There are three methods:

1. Add set xact_abort on at the beginning of the transaction statement

set xact_abort onbegin tranupdate statement 1 ...update statement 2 ...delete statement 3 ...commit trango

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

2. After each separate DML statement is executed, the execution status is immediately determined and processed accordingly.

begin tranupdate statement 1 ...if @@error <> 0begin rollback trangoto labendenddelete statement 2 ...if @@error <> 0begin rollback trangoto labendendcommit tranlabend:go

3. in SQL Server 2005, try... catch exception handling mechanism can be used.

begin tranbegin tryupdate statement 1 ...delete statement 2 ...endtrybegin catchif @@trancount > 0rollback tranend catchif @@trancount > 0commit trango

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

create procedure dbo.pr_tran_inproc as begin set nocount onbegin tranupdate statement 1 ...if @@error <> 0begin rollback tranreturn -1 enddelete statement 2 ...if @@error <> 0begin rollback tranreturn -1end commit tranreturn 0endgo

I hope this article will help you design SQL Server database programs.

Articles you may be interested in:

• How to Use transactions in SQL Server Stored Procedures
• Summary of SQL SERVER Stored Procedure Call
• Methods and Performance Comparison of five SQL Server paging stored procedures
• Example of using table values as input parameters in SQL Server Stored Procedures
• In-depth analysis of SQL Server Stored Procedures
• Sorting out the paging stored procedures of SQL Server 2000 and 2005
• SQL Server Stored Procedure calling in Java
• Differences between SQL Server user-defined functions and stored procedures
• SQL Server 2008 Stored Procedure example
• SQL Server uses a stored procedure to insert and update data.
• C # SQL Server transaction processing example