Menu
Can you start a transaction in one stored procedure and then roll it back or commit it in a nested procedure?
302
For example, suppose in your Stored Procedure you are running an Insert statement followed by Update statement. It is necessary that both these statements should complete successfully otherwise you should not run any of them. You can do such things using SQL Transaction.
Option 1: Use the scripting wizard Right-click the db --> tasks --> Generate scripts --> go through the wizard. Option 2: Open the stored procedures folder in SSMS (in the object explorer details window) You can use shift click to select all the stored procedures and you can then right_click and script them to a file.
We can use Transactions in Procedure whereas we can't use Transactions in Function.
Commit and rollback have different effects
This happens because SQL Server does not really support nested transactions.
If you commit or rollback in a nested stored proc (not transaction), then you'll generate error 266 because of a @@TRANCOUNT mismatch on start and entry
The rollback issue can be resolved by using SET XACT_ABORT ON which is "auto rollback" (simply) and suppresses error 266.
The commit issue... you can't as such. However, you can control where it happens by noting @@TRANCOUNT on stored proc entry and committing only if zero.
For correct transaction handling, see my answers here please: Nested stored procedures containing TRY CATCH ROLLBACK pattern? and Have I to count transactions before rollback one in catch block in T-SQL?
147
If you love us? You can donate to us via Paypal or buy me a coffee so we can maintain and grow! Thank you!
Donate Us With
