MySQL stored procedure
Posted by:
Apple Tan
Date: June 10, 2015 12:18AM
Firstly, I executed this code:
DROP PROCEDURE IF EXISTS `shs_create_table`;
DELIMITER $$
CREATE PROCEDURE shs_create_table (In projectId int, IN tableName varchar(100))
BEGIN
DECLARE colNum varchar(100) DEFAULT "";
DECLARE i int DEFAULT 1;
DECLARE MaxColsFetched int DEFAULT 600; -- set the max number of columns created in one table to be 600
select count(*)
into colNum
from trying_metadata
where project_id = projectId;
-- project has no more than 600 columns which can be created in one table
if colNum/MaxColsFetched <= 1 then
CALL shs_get_table_structure(projectId, tableName, MaxColsFetched, 1);
-- project has more than 600 columns which need more tables
else
while i <= ceiling(colNum/MaxColsFetched) do
if i=1 then
CALL shs_get_table_structure(projectId, concat(tableName, '_1'), MaxColsFetched, 1);
else
CALL shs_get_table_structure(projectId, concat(tableName, '_', i), MaxColsFetched, MaxColsFetched*(i-1));
end if;
set i = i+1;
end while;
end if;
END$$
DELIMITER ;
Secondly, I execute (project_id: 934 has 1344 entry, by right, by calling the below code, it will create several tables, each table stores 600 rows of data):
call shs_create_table (934,"try600");
Lastly, I do not know why I am always shown this error message:
Error Code: 1060. Duplicate column name 'pcode'. I checked and realised there is no pcode field name that exists at all.
I have tried Google on error code 1060 on duplicate 'pcode', but yet no avail. Hope someone could help me with this error I am facing.
Thank you in advance.