How to write a procedure to create table by inputting a variable date?

Question

Goal

I want to create table as follow, the format of table name is tbl+_+date , but I want to write a procedure to create when I input a given date.

drop table if exists tbl_20200802 ;
create table tbl_20200802 (index (USERID) )
select * from a;

Try

DELIMITER //
DROP PROCEDURE IF EXISTS pro_ljj_push_pre_id;
CREATE PROCEDURE pro_ljj_push_pre_id(
    IN test_date date
)
BEGIN
set @table_name=CONCAT('tbl_', test_date);


SET @t1 =CONCAT(
                            'drop table if exists '
                                ,@table_name 
                                ,';'
                                ,'\n'
                                ,'create table ', tab_name
                                , ' (index (USERID))'
                                ,'\n'
                                ,'SELECT * FROM a');
 PREPARE stmt3 FROM @t1;
 EXECUTE stmt3;
 DEALLOCATE PREPARE stmt3;
-- 
END //
DELIMITER ;

CALL pro_ljj_push_pre_id('20200802');

1054 - Unknown column 'tab_name' in 'field list'

Answer 1

You have some syntax error in your code, However, the following is your desired. Change the structure of table as you want.

DELIMITER $$
DROP PROCEDURE IF EXISTS pro_ljj_push_pre_id$$
CREATE PROCEDURE pro_ljj_push_pre_id(IN test_date VARCHAR(50))
BEGIN
set @table_name=CONCAT('tbl_', test_date);

 SET @t1 =CONCAT('drop table if exists ',@table_name);
 SELECT @t1;
 PREPARE stmt3 FROM @t1;
 EXECUTE stmt3;
 DEALLOCATE PREPARE stmt3;
 SET @t2 =CONCAT('create table ', @table_name, ' (id INT PRIMARY KEY, name VARCHAR(30));');
 SELECT @t2;
 PREPARE stmt4 FROM @t2;
 EXECUTE stmt4;
 DEALLOCATE PREPARE stmt4;
-- 
END $$
DELIMITER ;

CALL pro_ljj_push_pre_id('20200802');