BEGIN TRY;

    BEGIN TRAN;

    Exec StoredProc1; -- this stored proc perform insert
    Exec StoredProc2; -- this stored proc perform insert.Update

    COMMIT;

END TRY
BEGIN CATCH
    IF XACT_STATE() != 0 
        ROLLBACK TRAN;

    DECLARE @err_str VARCHAR(2048),
            @err_sev INT,
            @err_state INT;
        
    SELECT  @err_str = ERROR_MESSAGE(),
                @err_sev = ERROR_SEVERITY(),
                @err_state = ERROR_STATE();
            
    RAISERROR(@err_str, @err_sev, @err_state);
END CATCH;