MySQL - rolling back transactions in case of embedded procedures

Question

I try to rollback transaction in a procedure if an error occurs (using SIGNAL). I have two problems:

1. I want to stop executing Statement 1, if an error occurs
2. I want to stop executing Stateemtn 2, if an error occurs in procedure 1.

Procedure 1:

CREATE DEFINER=`edgar`@`%` PROCEDURE some_sp (IN param INT)
BEGIN

    DECLARE `_rollback` BOOL DEFAULT 0;

    DECLARE new_legal_entity_id INT;

    DECLARE specialty CONDITION FOR SQLSTATE '45000';
    DECLARE CONTINUE HANDLER FOR SQLEXCEPTION SET _rollback = 1;

    START TRANSACTION;

    CASE -- Some condition 
        BEGIN
            SIGNAL specialty
            SET MESSAGE_TEXT = 'Error message';
        END;
    ELSE BEGIN END;
    END CASE;

    INSERT INTO .... -- Statement 1

    COMMIT;
    END

Procedure 2:

CREATE DEFINER=`edgar`@`%` PROCEDURE some_sp_1 (IN param INT)
BEGIN

    DECLARE `_rollback` BOOL DEFAULT 0;

    DECLARE new_legal_entity_id INT;

    DECLARE specialty CONDITION FOR SQLSTATE '45000';
    DECLARE CONTINUE HANDLER FOR SQLEXCEPTION SET _rollback = 1;

    START TRANSACTION;

    CALL some_sp(some_param);
    INSERT INTO .... -- Statement 2

    COMMIT;
    END

I have read this and this , but still cannot get the code work

Answer 1

Try:

DELIMITER $$

DROP TABLE IF EXISTS `t`$$
DROP PROCEDURE IF EXISTS `some_sp_1`$$
DROP PROCEDURE IF EXISTS `some_sp`$$

CREATE TABLE `t`(
  `id` INT UNSIGNED
)$$

CREATE PROCEDURE `some_sp`(IN `param` INT)
BEGIN
  DECLARE `specialty` CONDITION FOR SQLSTATE '45000';
  IF `param` THEN
    SIGNAL `specialty`;
  END IF;
  INSERT INTO `t` SELECT 1;
END$$

CREATE PROCEDURE `some_sp_1`(IN `param` INT)
BEGIN
  DECLARE `_rollback` BOOL DEFAULT FALSE;
  DECLARE CONTINUE HANDLER FOR SQLEXCEPTION SET `_rollback` := TRUE;
  START TRANSACTION;
  CALL `some_sp`(`param`);
  IF `_rollback` THEN
    ROLLBACK;
  ELSE
    -- INSERT INTO `t1` SELECT 2; -- fail, t1 not exist
    INSERT INTO `t` SELECT 2;
    IF `_rollback` THEN
      ROLLBACK;
    ELSE
      COMMIT;
    END IF;
  END IF;
END$$

DELIMITER ;

CALL `some_sp_1`(0);
-- CALL `some_sp_1`(1);

SELECT `id` FROM `t`;