How can one transaction be successful in multiple transaction and one fails

In case there is an exception in the inner transaction then all the transactions which outer to that will get rollback.

i.e. if we have transaction1 inside that we have transaction 2 and after that we have transaction 3, and then transaction 3 gets committed after that some statement in transaction 2 fails then transaction 3 will remains as committed as it is already been committed, and the catch part for transaction 2 and transaction 3 is executed, same is explained via below diagram

create PROC usp_transaction_handle_multiple

AS

BEGIN

  BEGIN try

 BEGIN transaction trans1

   INSERT INTO input_values

     SELECT 'One', 'Two'

      BEGIN try

      BEGIN transaction trans2

      IF (( SELECT count(1) FROM input_values where col1='One') < 2>

           BEGIN

                  UPDATE input_values SET col2 ='updated two' WHERE col1='One'

                  COMMIT transaction trans1

                  PRINT 'Commit trans2 done'

           END

    ELSE

           BEGIN

           RAISERROR('No data to update',16,1)

           END

    END try

           BEGIN CATCH

PRINT 'ROLLBACK trans2 done in inner-block'

           ROLLBACK transaction trans2

           END CATCH

           IF (@@TRANCOUNT <> 0)

           BEGIN

           COMMIT transaction trans1

           PRINT 'Commit trans1 done'

           END

    END try

    BEGIN CATCH

    ROLLBACK transaction trans1

    PRINT 'ROLLBACK trans1 done in Outer-block'

    END CATCH

END

Executing statements:

select * from input_values

go

exec usp_transaction_handle_multiple

go

select * from input_values

create PROC usp_transaction_handle_multiple

AS

BEGIN

  BEGIN try

 BEGIN transaction trans1

   INSERT INTO input_values

     SELECT 'One', 'Two'

      BEGIN try

      BEGIN transaction trans2

      IF (( SELECT count(1) FROM input_values where col1='One') < 2>

           BEGIN

                  UPDATE input_values SET col2 ='updated two' WHERE col1='One'

                  COMMIT transaction trans1

                  PRINT 'Commit trans2 done'

           END

    ELSE

           BEGIN

           RAISERROR('No data to update',16,1)

           END

    END try

           BEGIN CATCH

PRINT 'ROLLBACK trans2 done in inner-block'

           ROLLBACK transaction trans2

           END CATCH

           IF (@@TRANCOUNT <> 0)

           BEGIN

           COMMIT transaction trans1

           PRINT 'Commit trans1 done'

           END

    END try

    BEGIN CATCH

    ROLLBACK transaction trans1

    PRINT 'ROLLBACK trans1 done in Outer-block'

    END CATCH

END

Executing statements:

select * from input_values

go

exec usp_transaction_handle_multiple

go

select * from input_values

create PROC usp_transaction_handle_multiple

AS

BEGIN

  BEGIN try

 BEGIN transaction trans1

   INSERT INTO input_values

     SELECT 'One', 'Two'

      BEGIN try

      BEGIN transaction trans2

      IF (( SELECT count(1) FROM input_values where col1='One') < 2>

           BEGIN

                  UPDATE input_values SET col2 ='updated two' WHERE col1='One'

                  COMMIT transaction trans1

                  PRINT 'Commit trans2 done'

           END

    ELSE

           BEGIN

           RAISERROR('No data to update',16,1)

           END

    END try

           BEGIN CATCH

PRINT 'ROLLBACK trans2 done in inner-block'

           ROLLBACK transaction trans2

           END CATCH

           IF (@@TRANCOUNT <> 0)

           BEGIN

           COMMIT transaction trans1

           PRINT 'Commit trans1 done'

           END

    END try

    BEGIN CATCH

    ROLLBACK transaction trans1

    PRINT 'ROLLBACK trans1 done in Outer-block'

    END CATCH

END

Executing statements:

select * from input_values

go

exec usp_transaction_handle_multiple

go

select * from input_values