I have an outer procedure which calls 5 different inner procedures something like
Parent procedure(Tran/try/catch) Inner sp1 (Tran/try/catch) ' ' Inner sp5 (Tran/try/catch)
But in this case, if one of my inner procedures is rolled back , the procedure is still committed because there are no errors in the outer procedures. How can I overcome this problem ? Thanks !!
Sorry my bad. The code looks something like:
CREATE PROC spInner AS BEGIN DECLARE @transactioncount INT SET @transactioncount = @@TRANCOUNT IF (@transactioncount > 0) BEGIN SAVE TRANSACTION savepoint1 END ELSE BEGIN BEGIN TRANSACTION BEGIN TRY DROP TABLE IF EXISTS #temp CREATE TABLE #temp (id INT NOT NULL) INSERT INTO #temp (id) SELECT NULL INSERT INTO #temp (id) SELECT 1 INSERT INTO #temp (id) SELECT 'A' IF (@transactioncount = 0) BEGIN COMMIT TRANSACTION END END TRY BEGIN CATCH IF (@transactioncount = 0) BEGIN ROLLBACK TRANSACTION END ELSE IF (XACT_STATE() <> -1) BEGIN ROLLBACK TRANSACTION savepoint1 END --logs errors into Error Log table PRINT ERROR_MESSAGE() END CATCH END
CREATE PROC spOuter AS BEGIN DECLARE @transactioncount INT SET @transactioncount = @@TRANCOUNT IF (@transactioncount > 0) BEGIN SAVE TRANSACTION savepoint2 END ELSE BEGIN BEGIN TRANSACTION BEGIN TRY Exec dbo.spInner IF (@transactioncount = 0) BEGIN COMMIT TRANSACTION END END TRY BEGIN CATCH IF (@transactioncount = 0) BEGIN ROLLBACK TRANSACTION END ELSE IF (XACT_STATE() <> -1) BEGIN ROLLBACK TRANSACTION savepoint2 END --logs errors into Error Log table PRINT ERROR_MESSAGE() END CATCH END
In this case the errors caught in the inner procedure is not caught in the outer procedure and outer procedure is committed though there are errors in inner procedures
The problem you’re facing is down to how the TRY…CATCH construct handles errors – it consumes them, eats them, and spits nothing back out except for a 0 return code. This is evidenced by a very innocuous line in the docs on TRY…CATCH that says
Errors trapped by a CATCH block are not returned to the calling application. If any part of the error information must be returned to the application, the code in the CATCH block must do so by using mechanisms such as SELECT result sets or the RAISERROR and PRINT statements.
This line however appears to be out of date and doesn’t give any information as to the THROW statement.
To resolve this problem, you can use the throw statement to re-throw the offending error after your rollback event, as seen in this fiddle (link) or in the abbreviated sample below:
BEGIN TRY BEGIN TRANSACTION --Your code goes here COMMIT TRANSACTION END TRY BEGIN CATCH --Some other error handling code here ROLLBACK TRANSACTION; THROW; END CATCH
The key is knowing what the CATCH block is doing, given the fact that when an error is raised in the TRY block, it is consumed (IE not passed back to the caller) allowing you to handle the error with your own code.
In your case, the CATCH block simply performs a rollback, meaning the error is never raised back to the calling procedure – adding THROW resolves that by manually throwing the error back to the caller (in your case the outer procedure)
If you don’t want to use throw, you can also use RAISERROR for some more granular control over the error that is raised.