Stored Procedure to create table
Hello all,
I am attempting to create an SP that will take three params and create a table using the params as the schema.tableName.
The purpose is to create many tables, all the same fields, but the names must change based on the date. Before asking why, these tables are auto-generated and filled with raw machine data. We are moving the tables to test them in another database, but doing so manually takes a lot of time.
Here is the code I have so far, I have added the @ symbol in the PREPARE stmt because Mysql gave me an error if I didn't use it.
CREATE PROCEDURE `param_test01`(IN p_schema varchar(30), IN p_year char(4), IN p_month char(2))
BEGIN
DECLARE t_name varchar(50);
DECLARE k_name varchar(50);
DECLARE t_create text;
set k_name = concat('sqlt_data_1_', p_year, '_', p_month, 't_stampndx');
if length(p_month) < 2 then
set p_month = concat('0',p_month);
end if;
set t_name = concat(p_schema, '.', 'sqlt_data_1_', p_year, '_', p_month);
set t_create = concat('create table ', t_name, ' (
`tagid` int(11) NOT NULL DEFAULT ''0'',
`intvalue` bigint(20) DEFAULT NULL,
`floatvalue` double DEFAULT NULL,
`stringvalue` varchar(255) DEFAULT NULL,
`datevalue` datetime DEFAULT NULL,
`dataintegrity` int(11) DEFAULT NULL,
`t_stamp` bigint(20) NOT NULL DEFAULT ''0'',
PRIMARY KEY (`tagid`,`t_stamp`),
KEY k_name (`t_stamp`)
)ENGINE=InnoDB DEFAULT CHARSET=utf8;');
prepare stmt from @t_create;
execute stmt;
deallocate prepare stmt;
END
Any ideas?
Thank you