SQL Server Stored Procedure nested transaction processing

Source: Internet
Author: User
A stored procedure may be called independently or nested by other stored procedures. nested transactions may occur. The following is a general code for nested calling of stored procedures. If you cannot determine whether the stored procedure may be called by other stored procedures, we recommend that you create createprocproc_exam for each stored procedure according to this template.

A stored procedure may be called independently or nested by other stored procedures. nested transactions may occur. The following is a general code for nested calling of stored procedures. If you cannot determine whether a stored procedure may be called by other stored procedures, create proc proc_exam is recommended for each stored procedure according to this template.

A stored procedure may be called independently or nested by other stored procedures. nested transactions may occur.

The following is a general code for nested calling of stored procedures. If you cannot determine whether the stored procedure may be called by other stored procedures, we recommend that you create a template for each stored procedure.

Create proc proc_exampleas begin -- declare variables, store the number of currently enabled transactions declare @ exist_trancount int select @ exist_trancount = @ trancount if @ exist_trancount> 0 -- create a transaction save point transaction tran_proc else -- start the new transaction begin transaction tran_proc/ * stored procedure business processing code · */if @ error <> 0 goto error if @ exist_trancount = 0 -- commit transaction commit tran tran_proc return 1 error: -- rollback transaction or transaction storage point rollback transaction tran_proc return-1end

Explanation:

1) First, determine whether the execution of the current Stored Procedure instance is a nested transaction call. If it is nested, the value of @ TRANCOUNT determined by the stored procedure should be greater than 0.Transaction retention pointInstead of starting new transactions. If not nested transactions@ TRANCOUNTIt should be 0. Now you can start a new transaction.

2) then, it is the business processing code of the stored procedure itself. to process the code in each step, you need to determine @ ERROR. If <> 0, execute the error code.

3) At last, if the processing is correct and the call is not nested, the transaction is committed. If an error occurs, the transaction is rolled back or the storage point is rolled back.

Of course, when calling a stored procedure, you need to determine the return value of the stored procedure for corresponding processing.

If something is wrong, please make a brick. If you have other methods, please share them. Thank you! O (partition _ partition) O

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.