Keep local variable after rollback in mysql
It's not because of ROLLBACK. Value is reset because error is thrown later during INSERT. Handling the error inside procedure body (for instance, with DECLARE CONTINUE HANDLER FOR SQLEXCEPTION ) will give you what you want.  Alternatively, in your case you can just LEAVE inside IF @uid IS NOT NULL THEN block :  

CREATE PROCEDURE sp_insert_values(IN uid INT, IN val_from INT, IN val_to INT, OUT retval VARCHAR(25) )
proc_body:
BEGIN
    SET retval = 'ERROR_UNKNOWN';
    START TRANSACTION;
    SET @val = val_from;
    REPEAT
        -- is there already record, registered to some user?
        SET @uid = (SELECT user_id FROM test WHERE value = @val);
        IF @uid IS NOT NULL THEN
            SET retval = 'ERROR_ANOTHER_USER';
            -- is it already registered to me?
            IF @uid = uid THEN
                SET retval = 'ERROR_ALREADY_HAS';
            END IF;

            ROLLBACK;
            LEAVE proc_body;
        END IF;
        INSERT INTO test (value, user_id) VALUES (@val, uid);
        SET @val = @val + 1;
    UNTIL @val &gt; val_to END REPEAT;
    SET retval = 'OK';
    COMMIT;
END proc_body;