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

This article mainly introduces "what are the methods of SQLServer writing transaction code in stored procedures". In daily operations, I believe that many people have doubts about the methods of writing transaction codes in SQLServer stored procedures. The editor consulted all kinds of materials and sorted out simple and useful operation methods. I hope it will be helpful for you to answer the question of "what are the ways for SQLServer to write transaction code in stored procedures?" Next, please follow the editor to study!

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

Begintranupdatestatement1...updatestatement2...deletestatement3...committran

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

Createtabledemo (idintnotnull) gobegintraninsertintodemovalues (null) insertintodemovalues (2) committrango

An error message that violates the notnull constraint appears during execution, but then prompts (1row (s) affected). After we execute select*fromdemo, we find that insertintodemovalues (2) executes successfully. What is the reason for this? it turns out that when a runtime error occurs in SQLServer, by default rollback will cause the wrong statement, and continue to execute the subsequent statement.

What are the methods of writing transaction code in stored procedures by SQLServer

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

1. Add setxact_aborton at the beginning of the transaction statement

Setxact_abortonbegintranupdatestatement1...updatestatement2...deletestatement3...committrango

When the xact_abort option is on, SQLServer 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.

Begintranupdatestatement1...if@@error0beginrollbacktrangotolabendenddeletestatement2...if@@error0beginrollbacktrangotolabendendcommittranlabend:go

3. In SQLServer2005, the try...catch exception handling mechanism is available.

Begintranbegintryupdatestatement1...deletestatement2...endtrybegincatchif@@trancount > 0rollbacktranendcatchif@@trancount > 0committrango

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

Createproceduredbo.pr_tran_inprocasbeginsetnocountonbegintranupdatestatement1...if@@error0beginrollbacktranreturn-1enddeletestatement2...if@@error0beginrollbacktranreturn-1endcommittranreturn0endgo

At this point, the study on "what are the methods for SQLServer to write transaction code in stored procedures" is over. I hope to be able to solve your doubts. The collocation of theory and practice can better help you learn, go and try it! If you want to continue to learn more related knowledge, please continue to follow the website, the editor will continue to work hard to bring you more practical articles!

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.