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.
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