MYSQL declaring variables

Question

I don't get what is wrong with this script

BEGIN
DECLARE crs INT DEFAULT 0;

WHILE crs < 10 DO
INSERT INTO `continent`(`name`) VALUES ('cont'+crs)
SET crs = crs + 1;
END WHILE;
END;

I want it to insert 10 values into the table continent but there is an error at the second line.

Answer 1

declare variable in MySQL with @ and assign with :=

SET @crs = 0; // declaration
--here your query
@crs := @crs+1 // assignment

References

• user defined variables
• assignment

Answer 2

MySQL does not support the execution of anonymous blocks of stored procedure code.

You need to create a stored procedure including that code and then invoke it.

Also, you were missing the semi-colon at the end of your insert statements. I fixed that. You also probably want to use concat() instead of + to generate the names, but I'll leave that change to you.

Create the procedure:

DELIMITER $$

DROP PROCEDURE IF EXISTS insert_ten_rows $$

CREATE PROCEDURE insert_ten_rows () 
    BEGIN
        DECLARE crs INT DEFAULT 0;

        WHILE crs < 10 DO
            INSERT INTO `continent`(`name`) VALUES ('cont'+crs);
            SET crs = crs + 1;
        END WHILE;
    END $$

DELIMITER ;

Invoke the procedure:

CALL insert_ten_rows();