Return empty table after rollback

Question

Im writing a procedure, I want it to return table if there wasnt any rollbacks, and return empty table / nothing if there was at least 1 rollback.

DELIMITER $$
CREATE PROCEDURE payment(IN amount int, IN profession varchar(50))
BEGIN    
DECLARE done BOOL DEFAULT FALSE;
DECLARE salary INT;
DECLARE pes VARCHAR(11);
DECLARE summary INT DEFAULT 0;

DECLARE employee_cursor CURSOR FOR (SELECT RIGHT(PESEL,3), pensja FROM Pracownicy WHERE zawod=profession);
DECLARE CONTINUE HANDLER FOR NOT FOUND SET done = TRUE;

DROP TEMPORARY TABLE IF EXISTS tmp1;
CREATE TEMPORARY TABLE tmp1(pesel char(11), status varchar(20) DEFAULT 'wyplacono');

SET AUTOCOMMIT =0;
START TRANSACTION;
    OPEN employee_cursor;
    readLoop : LOOP
        FETCH employee_cursor INTO pes,salary;
        IF done THEN
            LEAVE readLoop;
        END IF;

        SET summary = summary + salary;

        IF( summary > amount ) THEN
            ROLLBACK;
        END IF;

        INSERT INTO tmp1(pesel) VALUES(CONCAT('********',pes));

    END LOOP;
    CLOSE employee_cursor;
    COMMIT;
    SELECT * from tmp1;    
END $$
DELIMITER ;

As far it works fine when it doesnt rollback, but

INSERT INTO tmp1(pesel) VALUES(CONCAT('********',pes));

seems to ignore transaction :/

Answer 1

move the rollback test to outside the loop.