MySQL procedure to create a table each time another procedure is called (merging two scripts)

The attached code is a MySQL procedure.  It fills the row of one table (node_) with rows of another table (master_) based on a series of conditions. Here is the creation script for node_ table:

SELECT @tblname:=concat('node_',date_format(now(),'%Y%m%d%H%i%S'),'');
SELECT @cr := concat('CREATE TABLE ',@tblname,' (
  `node_id`  INT(5),
  `node_code`  VARCHAR(4),
  `node_1st`  VARCHAR(1),
  `node_2nd`  VARCHAR(1),
  `node_3rd`  VARCHAR(2),
  `node_act`  VARCHAR(1),
  `node_ref`  INT(4),
  `node_dur`  INT(4),
  `node_suc`  INT(4),
  `node_log`  VARCHAR(2),
  `node_lag`  INT(4),
  `node_con`  INT(4))','');
PREPARE stmt from @cr;
EXECUTE stmt;
DEALLOCATE PREPARE stmt;

Now what i need to know how to do is how to have the attached code incorporate the above creation scipt, in such that each time the attached code is called, a new table is created.

So how do I do that?
create procedure WALKTHROUGH() 
 
BEGIN 
 
DECLARE rowwalker CURSOR as select * from "master_" order by primary_key ASC; 
 
OPEN rowwalker; 


read_loop:  LOOP 

WHILE   rowwalker.PRE iS not null DO 
        fetch rowwalker; 
	IF done THEN 
   		LEAVE read_loop; 
	END IF; 
end while; 


INSERT INTO NODE_ (node_act, node_ref, node_suc, node_log, node_lag, node_con) 

           VALUES ('1', rowwalker.REF, rowwalker.SUC, rowwalker.LOG, rowwalker.LAG, rowwalker.CON); 


INSERT INTO NODE_ (node_act, node_ref, node_suc, node_log, node_lag, node_con) 
           VALUES (SELECT '1', REF, SUC, LOG, LAG, CON FROM master_ WHERE SUC = rowwalker.SUC);


IF done THEN 
   LEAVE read_loop; 
END IF; 

END LOOP; 
 
 
CLOSE rowwalker; 
 
END;

Open in new window

marcoullisAsked:
Who is Participating?
 
arnoldCommented:
Based on discussion and the need/use of variables, you may have to repeat the same method you used in the create table directive in your insert statements.
http://forums.mysql.com/read.php?98,103422,103422#msg-103422

not sure the syntax is correct, but it might give you an idea on how to implement it.

create procedure WALKTHROUGH() 
 
BEGIN 
use database_name;
Declare @tblname type varchar(30);

Declare @master_table type varchar(30); 

set @master_table:='master_tablename';

SET @tblname:=concat('node_',date_format(now(),'%Y%m%d%H%i%S'),'');
SET @cr := concat('CREATE TABLE if not exists ',@tblname,' (
  `node_id`  INT(5),
  `node_code`  VARCHAR(4),
  `node_1st`  VARCHAR(1),
  `node_2nd`  VARCHAR(1),
  `node_3rd`  VARCHAR(2),
  `node_act`  VARCHAR(1),
  `node_ref`  INT(4),
  `node_dur`  INT(4),
  `node_suc`  INT(4),
  `node_log`  VARCHAR(2),
  `node_lag`  INT(4),
  `node_con`  INT(4))','');
PREPARE stmt from @cr;
EXECUTE stmt;
DEALLOCATE PREPARE stmt;
 
DECLARE rowwalker CURSOR as select * from "master_" order by primary_key ASC; 
 
OPEN rowwalker; 


read_loop:  LOOP 

WHILE   rowwalker.PRE iS not null DO 
        fetch rowwalker; 
	IF done THEN 
   		LEAVE read_loop; 
	END IF; 
end while; 


set @insert_node := concat("INSERT INTO ",@tblname, " (node_act, node_ref, node_suc, node_log, node_lag, node_con) 

           VALUES ('1', rowwalker.REF, rowwalker.SUC, rowwalker.LOG, rowwalker.LAG, rowwalker.CON)";
Prepare stmt from @insert_node;
execute stmt;
deallocate prepare stmt  


set @insert_node := concat("INSERT INTO ",@tblname, "  (node_act, node_ref, node_suc, node_log, node_lag, node_con) 
           VALUES (SELECT '1', REF, SUC, LOG, LAG, CON FROM ", @master_table, " WHERE SUC = rowwalker.SUC)";
prepare stmt from @insert_node;
execute stmt;
deallocate prepare stmt;


IF done THEN 
   LEAVE read_loop; 
END IF; 

END LOOP; 
 
 
CLOSE rowwalker; 
 
END;

Open in new window

0
 
marcoullisAuthor Commented:
please note that currently the attached script incorrectly references "NODE_"

how can we make the procedure know to apply the attached code to the right table?
0
 
marcoullisAuthor Commented:
or is that not possible?
0
Get expert help—faster!

Need expert help—fast? Use the Help Bell for personalized assistance getting answers to your important questions.

 
arnoldCommented:
You could place it at the top of the procedure right after begin and replace the reference to NODE_ with the variable @tblname.

Depending on the frequency with which this procedure runs, a check to see whether the table already exists as to avoid issuing create table when the table already exists

add  if not exist
create table if not exists @tblname
Presumably this is what caused the SP to abort when the creation was included due to creation table error.
0
 
marcoullisAuthor Commented:
could you suggest the correct code pls?
0
 
marcoullisAuthor Commented:
OK i will accept the solution because a real effort was made, but it didn't work when i tried to implement it... but i don't know why. will open a new question to filter out the reason it didn't work.
0
 
marcoullisAuthor Commented:
A solid effort, but on implementation in Toad for MySQL it did not work. Will proceed to open a new question to filter out the problem.
0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.