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

Source: Internet
Author: User
Tags stored procedure example

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

Related Article

Contact Us

The content source of this page is from Internet, which doesn't represent Alibaba Cloud's opinion; products and services mentioned on that page don't have any relationship with Alibaba Cloud. If the content of the page makes you feel confusing, please write us an email, we will handle the problem within 5 days after receiving your email.

If you find any instances of plagiarism from the community, please send an email to: info-contact@alibabacloud.com and provide relevant evidence. A staff member will contact you within 5 working days.

A Free Trial That Lets You Build Big!

Start building with 50+ products and up to 12 months usage for Elastic Compute Service

  • Sales Support

    1 on 1 presale consultation

  • After-Sales Support

    24/7 Technical Support 6 Free Tickets per Quarter Faster Response

  • Alibaba Cloud offers highly flexible support services tailored to meet your exact needs.