Mysql Stored procedure to create table

Question

Plzzz Answer

I want to create a table in a stored procedure But tabel name is stored in a variable. Example

 create procedure ABC 
(
@tablename. // is a parameter in which table       
                                name is stored
)
AS
     BEGIN

Create table @tablename
(
   Colmn one....
   Colmn. two...
    ...............
)

End

but error is shown where
table @tablename

Q ) how can I fix this

I want to set a table name which is stored in a variable @tablename

This link will help you. https://stackoverflow.com/questions/190776/how-to-have-dynamic-sql-in-mysql-stored-procedure — Biju Kalanjoor, Aug 21 '20 at 14:44

score 0 · Answer 1 · answered Aug 21 '20 at 14:50

You can't do that this way, You need prepared statements

DROP procedure IF EXISTS `ABC`;

DELIMITER $$

CREATE DEFINER=`root`@`%` PROCEDURE `ABC`(
IN _tablename VARCHAR(50) -- is a parameter in which table       
                            --    name is stored
)
BEGIN
SET @sql = CONCAT('
Create table ',_tablename,'
(
   Colmn1 INT,
   Colmn2 INT

);');
PREPARE stmt FROM @sql;
EXECUTE stmt;
DEALLOCATE PREPARE stmt;
End$$

DELIMITER ;

Mysql Stored procedure to create table

1 Answers1