DELIMITER $$
CREATE PROCEDURE generate_venue_seats()
COMMENT 'Generates venue_seats rows A-U, seat_number 1 through each row''s last seat, as hardcoded below'
BEGIN
    DECLARE v_row_code  INT DEFAULT 65;   -- ASCII 'A'
    DECLARE v_row_char  CHAR(1);
    DECLARE v_last_seat INT;
    DECLARE v_seat_num  INT;

    -- for Robinson Conference Room, venue_id=1
        SET v_seat_num = 1;
        WHILE v_seat_num <= 12 DO
            INSERT INTO venue_seats (`row`, seat_number, venue_id)
            VALUES ('-', v_seat_num, 1);

            SET v_seat_num = v_seat_num + 1;
        END WHILE;    

    -- for Pfleeger, venue_id=2
    WHILE v_row_code <= 85 DO            -- ASCII 'U'
        SET v_row_char = CHAR(v_row_code);

        -- Last seat number per row, as supplied
        SET v_last_seat = CASE v_row_char
            WHEN 'A' THEN 16
            WHEN 'B' THEN 22
            WHEN 'C' THEN 31
            WHEN 'D' THEN 32
            WHEN 'E' THEN 33
            WHEN 'F' THEN 32
            WHEN 'G' THEN 36
            WHEN 'H' THEN 27
            WHEN 'I' THEN 38
            WHEN 'J' THEN 38
            WHEN 'K' THEN 38
            WHEN 'L' THEN 42
            WHEN 'M' THEN 11
            WHEN 'N' THEN 45
            WHEN 'O' THEN 46
            WHEN 'P' THEN 49
            WHEN 'Q' THEN 50
            WHEN 'R' THEN 51
            WHEN 'S' THEN 51
            WHEN 'T' THEN 52
            WHEN 'U' THEN 48
        END;

        SET v_seat_num = 1;
        WHILE v_seat_num <= v_last_seat DO
            INSERT INTO venue_seats (`row`, seat_number, venue_id)
            VALUES (v_row_char, v_seat_num, 2);

            SET v_seat_num = v_seat_num + 1;
        END WHILE;

        SET v_row_code = v_row_code + 1;
    END WHILE;
END$$
DELIMITER ;