Generate Codes Stored Procedure

DELIMITER ;;
DROP PROCEDURE `generate_codes`;;
CREATE DEFINER=`root`@`localhost` PROCEDURE `generate_codes`(
    IN bf_allocation_num INT
)
BEGIN

    SET @Allocation = bf_allocation_num;
    SET @Tmptable = CONCAT( 'tmp_code_generation_jobs_', @Allocation );
    SET @Canceled = 0;

    SET @qry_vars = "SELECT
        `cj`.`code_count`,
        `s`.`prefix`,
        `c`.`set`,
        LENGTH(`c`.`set`)
    FROM
        `code_generation_jobs` AS `cj`

    JOIN `skus` AS `s` ON `s`.`id` = `cj`.`sku_id`
    JOIN `charsets` AS `c` ON `c`.`id` = `cj`.`charset_id`

    WHERE `cj`.`id` = @Allocation

    INTO
        @Codes,
        @Prefix,
        @Chars,
        @CChars";

    PREPARE qry_vars_stmt FROM @qry_vars;
    EXECUTE qry_vars_stmt;
    DEALLOCATE PREPARE qry_vars_stmt;

    SET @qry_status1 = CONCAT('CREATE TABLE `', @Tmptable, '` (count int) ENGINE = MEMORY');
    PREPARE qry_status_stmt1 FROM @qry_status1;
    EXECUTE qry_status_stmt1;
    DEALLOCATE PREPARE qry_status_stmt1;

    SET @qry_status2 = CONCAT('TRUNCATE TABLE `', @Tmptable, '`');
    SET @qry_status3 = CONCAT('INSERT INTO `', @Tmptable, '` (`count`) VALUES (@Codes)');
    SET @qry_status4 = CONCAT('DROP TABLE `', @Tmptable, '`');
    PREPARE qry_status_stmt2 FROM @qry_status2;
    PREPARE qry_status_stmt3 FROM @qry_status3;
    PREPARE qry_status_stmt4 FROM @qry_status4;


    SET @qry_rand = "SELECT CONCAT(
        SUBSTRING(@Chars, 0.5 + RAND() * @CChars, 1),
        SUBSTRING(@Chars, 0.5 + RAND() * @CChars, 1),
        SUBSTRING(@Chars, 0.5 + RAND() * @CChars, 1),
        SUBSTRING(@Chars, 0.5 + RAND() * @CChars, 1),
        SUBSTRING(@Chars, 0.5 + RAND() * @CChars, 1),
        SUBSTRING(@Chars, 0.5 + RAND() * @CChars, 1),
        SUBSTRING(@Chars, 0.5 + RAND() * @CChars, 1),
        SUBSTRING(@Chars, 0.5 + RAND() * @CChars, 1),
        SUBSTRING(@Chars, 0.5 + RAND() * @CChars, 1),
        SUBSTRING(@Chars, 0.5 + RAND() * @CChars, 1),
        SUBSTRING(@Chars, 0.5 + RAND() * @CChars, 1),
        SUBSTRING(@Chars, 0.5 + RAND() * @CChars, 1)
    ) INTO @Random";
    PREPARE qry_rand_stmt FROM @qry_rand;

    SET @qry_insert = CONCAT('INSERT IGNORE INTO `codes_', @Prefix, '` (`code`,`allocation`) VALUES ( @Random, @Allocation )');
    PREPARE qry_insert_stmt FROM @qry_insert;

    SET @qry_complete = "UPDATE `code_generation_jobs` SET `status` = 'complete' WHERE `id` = @Allocation";
    PREPARE qry_complete_stmt FROM @qry_complete;

    SET @qry_canceled = "SELECT IF( `status` = 'canceled', 1, 0 ) INTO @Canceled FROM `code_generation_jobs` WHERE `id` = @Allocation";
    PREPARE qry_canceled_stmt FROM @qry_canceled;

    START TRANSACTION;

    code_gen_loop: WHILE @Codes > 0 DO

        IF MOD( @Codes, 10000 ) = 0 THEN

            EXECUTE qry_status_stmt2;
            EXECUTE qry_status_stmt3;
            EXECUTE qry_canceled_stmt;

            SAVEPOINT foo;
            ROLLBACK TO SAVEPOINT foo;

            IF @Canceled = 1 THEN
                ROLLBACK;
                LEAVE code_gen_loop;
            END IF;

        END IF;

        EXECUTE qry_rand_stmt;
        EXECUTE qry_insert_stmt;

        SET @Codes = @Codes - ROW_COUNT();

    END WHILE;

    IF @Canceled = 0 THEN
        COMMIT;
        EXECUTE qry_complete_stmt;
    END IF;

    EXECUTE qry_status_stmt4;

    DEALLOCATE PREPARE qry_rand_stmt;
    DEALLOCATE PREPARE qry_insert_stmt;
    DEALLOCATE PREPARE qry_status_stmt2;
    DEALLOCATE PREPARE qry_status_stmt3;
    DEALLOCATE PREPARE qry_status_stmt4;
    DEALLOCATE PREPARE qry_complete_stmt;

END;;
DELIMITER ;