I am trying to create a Procedure during the creation of a database, the mysql query is as follows:
CREATE DATABASE database_Sensor1;
USE database_Sensor1;
CREATE TABLE Persons(id int NOT NULL);
DELIMITER //
CREATE PROCEDURE MYLOOP() IF database_Sensor1 EXISTS
BEGIN
DECLARE i int;
DECLARE str varchar(255);
SET i = 0;
WHILE i < 32 DO
SET str = CONCAT('col_',i);
SET @sql = CONCAT('ALTER TABLE persons ADD ',str,' float;');
SET i = i + 1;
PREPARE stmt FROM @sql;
EXECUTE stmt;
DEALLOCATE PREPARE stmt;
END WHILE;
END
DELIMITER ;
CALL MYLOOP();
But I get this error:
#1305 - PROCEDURE database_sensor1.MYLOOP does not exist
I am trying to use the Procedure to Loop the table creation by quickly altering the table to add 32 columns in so that I can run another .php to add in the values.
CREATE DATABASE database_Sensor1;
USE database_Sensor1;
CREATE TABLE database_Sensor1.Persons(id int NOT NULL);
DELIMITER //
CREATE PROCEDURE database_Sensor1.MYLOOP()
BEGIN
DECLARE i int;
DECLARE str varchar(255);
SET i = 0;
WHILE i < 32 DO
SET str = CONCAT('col_',i);
SET @sql = CONCAT('ALTER TABLE database_Sensor1.persons ADD ',str,' float;');
SET i = i + 1;
PREPARE stmt FROM @sql;
EXECUTE stmt;
DEALLOCATE PREPARE stmt;
END WHILE;
END
//
DELIMITER ;
CALL MYLOOP();
Editions:
IF database_name EXISTS
construction not exists. Removed. See edition 3.