Why does my procedure have error when I’m trying to do loop?

i try to do loop to load data in MySQL Workbench 8.0. but it keep error and i cannot figure it out.

here is my code

DROP TABLE IF EXISTS Cars;
CREATE TABLE Cars (
Id INT NOT NULL PRIMARY KEY AUTO_INCREMENT,
Name CHAR (50) NOT NULL,
Price INT
);

DROP PROCEDURE IF EXISTS load_car;

DELIMITER $$
CREATE PROCEDURE load_car()
    BEGIN
        DECLARE counter int;
        SET counter = 1;
        START transaction;
    WHILE counter <= 10 DO
    BEGIN
        INSERT INTO Cars 
        VALUES 
        ('Car-'+CAST(counter as varchar), counter*100);
        SET counter = counter + 1;
    END;
    END WHILE;
END
$$
DELIMITER ;

Answer

You cant use VARCHAR() in CAST(). You need to change that to CHAR() and with length defined. Try this:

DROP TABLE IF EXISTS Cars;
CREATE TABLE Cars (
Id INT NOT NULL PRIMARY KEY AUTO_INCREMENT,
Name CHAR (50) NOT NULL,
Price INT
);

DROP PROCEDURE IF EXISTS load_car;

DELIMITER $$
CREATE PROCEDURE load_car()
    BEGIN
        DECLARE counter int;
        SET counter = 1;
        START transaction;
    WHILE counter <= 10 DO
    BEGIN
        INSERT INTO Cars 
        VALUES 
        ('Car-'+CAST(counter AS CHAR(255)), counter*100);
        SET counter = counter + 1;
    END;
    END WHILE;
END
$$
DELIMITER ;