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