Shulou(Shulou.com)05/31 Report--

This article shows you the methods of writing transactions in SQL Server stored procedures, which are concise and easy to understand, which will definitely brighten your eyes. I hope you can get something through the detailed introduction of this article.

An example is given to describe the method of writing transactions in SQL Server stored procedures. The details are as follows:

Database transaction processing is very useful in SQL Server. In view of the fact that there are often loopholes in the transaction code written by many SQL beginners, this paper introduces three different methods and gives an example of how to write correct code in stored procedure transaction processing. I hope it can be helpful to you.

When writing stored procedure code related to SQL Server transactions, you often see the following:

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

There are great hidden dangers in SQL written in this way. Take a look at the following example:

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

An error message that violates the not null constraint appears during execution, but then prompts (1 row (s) affected). After we execute select * from demo, we find that insert into demo values (2) executes successfully. What is the reason for this? It turns out that when a runtime error occurs in SQL Server, it defaults to rollback the statement that causes the error, and continues to execute subsequent statements.

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

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 execution and rollback the entire transaction when it encounters an error.

two。 Immediately after each individual DML statement is executed, the execution status is judged 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, the try...catch exception handling mechanism is available.

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 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 these are the ways to write transactions in SQL Server stored procedures. Have you learned any knowledge or skills? If you want to learn more skills or enrich your knowledge reserve, you are welcome to follow the industry information channel.

Welcome to subscribe "Shulou Technology Information " to get latest news, interesting things and hot topics in the IT industry, and controls the hottest and latest Internet news, technology news and IT industry trends.