NiCU NiCU - 7 months ago 20
SQL Question

MYSQL declaring variables

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

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();
Comments