CODEWITHSUNDEEP
mysqlselect
SamanthaAlexandria
SamanthaAlexandria

Reputation: 225

How can I set a variable as a `table` in a procedure?

Table t2 has 2 columns (id, tablename) with these values (1,2,3) x (toto, titi, tata):

id|tablename|
1 |toto|
2 |tata|
3 |titi|

I'd like to create a procedure which has id as a user-variable input and does this:

BEGIN 

 CREATE TABLE `newtable` AS (
 SELECT * FROM (SELECT tablename from t2 WHERE id=2); 
     );
END

The results should act as if the query was: 

BEGIN
CREATE TABLE `newtable` AS (
SELECT * FROM `tata`
);
 END

Upvotes: 0

Views: 53

Answers (1)

Dark Knight
Dark Knight

Reputation: 6541

DELIMITER $$

CREATE DEFINER=`root`@`localhost` PROCEDURE `pr_create_table_by_reference`(
    IN `str_new_table_name` VARCHAR(50),
    IN `int_id` INT

)
LANGUAGE SQL
NOT DETERMINISTIC
CONTAINS SQL
SQL SECURITY DEFINER
COMMENT ''
BEGIN

    DECLARE STR_TABLE_NAME VARCHAR(1000) DEFAULT NULL;

    SELECT tablename INTO STR_TABLE_NAME FROM t2 WHERE id = int_id LIMIT 1;

    SET @prep_stmt = CONCAT('CREATE TABLE ', str_new_table_name ,' as ( SELECT * FROM ',  STR_TABLE_NAME , ')');

    PREPARE stmt FROM @prep_stmt; 
    EXECUTE stmt; 
    DEALLOCATE PREPARE stmt;
END

$$

Procedure Call:

SET @new_table:= 'newTable';
SET @id:= 2;
CALL `pr_create_table_by_reference`(@new_table, @id);

OR 

CALL `pr_create_table_by_reference`('newTable', 2);

Upvotes: 1

Related Questions