MySQL Forums
Forum List  »  Stored Procedures

Stored Procedure to create table
Posted by: Mike Demaris
Date: July 13, 2022 02:54PM

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

Options: ReplyQuote


Subject
Views
Written By
Posted
Stored Procedure to create table
221
July 13, 2022 02:54PM


Sorry, only registered users may post in this forum.

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.