question

Bab avatar image
Bab asked

ROLLBACK TRANSACTION

I have some store procedures as follows: Create Proc Export as BEGIN TRANSACTION sp_Export Exec sp_export --1 COMMIT TRANSACTION sp_Export ERROR_HANDLER: IF @@TRANCOUNT != 0 ROLLBACK TRANSACTION Create Proc sp_Export as --1 Exec Sp_exportHeader –2 Exec Sp_exportHeaderExtras Create Proc Sp_exportHeader as --2 Exec Sp_exportDetail –3 Exec Sp_exportDetailExtras Create Proc Sp_exportdetail as--3 Exec Sp_second_detail –4 Exec Sp_ second_detail_Extras I have these store procedures one calling after another. I have begin transaction/commit transaction on the very first store procedure. If something goes wrong in any other store procedures my intention is to rollback everything. But its not happening. I tried to add begin/commit transaction on the other procedures but if something goes wrong I get the error like missing rollback/commit. Wondering if anyone know how to rollback if any issue is occur in any of the store procedures. I went through other forums too and found that lots of other people are struggling with this issue too. Any help appreciated.
rollback
10 |1200

Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.

1 Answer

·
Kev Riley avatar image
Kev Riley answered
In each of your sub procedures, you could start a transaction, and commit it if there is no error. Then after all procs have run, in the outer proc, check for @@TRANCOUNT, and if this is not 1, then rollback, or commit the lot
10 |1200

Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.

Write an Answer

Hint: Notify or tag a user in this post by typing @username.

Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.