MySQL Forums
Forum List  »  Newbie

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.

Options: ReplyQuote


Subject
Written By
Posted
MySQL stored procedure
June 10, 2015 12:18AM


Sorry, you can't reply to this topic. It has been closed.

Content reproduced on this site is the property of the respective copyright holders. It is not reviewed in advance by Oracle and does not necessarily represent the opinion of Oracle or any other party.