Creating a Procedure prior to the creation of database and table

Question

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.

Akina · Accepted Answer · 2021-03-02T05:17:14.190

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.
Procedure creation code is not finalized with delimiter. Added.
The database in which the procedure must be created is not specified explicitly. Added. If the database not exists then the error will be generated. The same with the table name.

Creating a Procedure prior to the creation of database and table

1 Answers1

Linked