Unanswered: Creating stored procedure from MYPHPADMIN.
Server version: 5.5.39 OS = Windows 8.1
I know this is odd but Im trying to create a stored procedure to check the number of records in a table and if it is at my limit add a new table to the merge.
for testing purposes i am using 5 in reality will be much larger(10M) This will be done with a before insert trigger.
The purpose is to temporarily get past a database file size limit without recoding entire project. eventually this will all move to a different server and all of the tables will be combined.
here is what I have so far.
CREATE PROCEDURE autoexpanddb() BEGIN DECLARE mylasttable, mycount INT DEFAULT 0; DECLARE myquery VARCHAR(40); set mylasttable:= (select lasttable from `dbsettings`); set myquery = concat("set mycount:= select COUNT(*) FROM `funny",lasttable,"`"); PREPARE stmt FROM myquery; EXECUTE stmt; DEALLOCATE PREPARE stmt; if mycount%5= 0 THEN set mylasttable:= (mylasttable+1); update `dbsettings` SET lasttable = mylasttable; DECLARE alterstring VARCHAR(255) DEFAULT "alter table funnym1 union = ("; SET mycount = 1; WHILE mycount < mylasttable DO set alterstring: = concat(alterstring, "funny",mycount, ","); set mycount:= (mycount+1); END WHILE; set alterstring: = concat(alterstring, "funny",mycount, " insert_method = LAST"); PREPARE stmt FROM alterstring; EXECUTE stmt; DEALLOCATE PREPARE stmt; end if; END //
DELIMITER is set to //
getting the following error
#1064 - You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near 'myquery;
DEALLOCATE PREPARE stmt;
if mycount%5= 0 THEN
set myl' at line 7
Probably something simple, lack of sleep. Thank you in advance if anyone sees what I'm missing.
Last edited by n1ghtowl; 03-29-15 at 03:07.
Reason: updated code, better explanation