Inner procedure is rolled back but outer procedure is still committed

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:
–INNER PROCEDURE

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

–Outer procedure

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.