CODEWITHSUNDEEP
mysqlsqlstored-procedures
Menelaos
Menelaos

Reputation: 25725

MySQL stored procedure Error - Create table from param names

I'm trying the following but I'm getting the following error:

ERROR 1054 (42S22): Unknown column 'f' in 'where clause'

I'm seriously confused because f is a parameter of createtableTest...

CREATE PROCEDURE createtableTest
(
tname2 varchar(20),
f varchar(20)
)
BEGIN
DROP TABLE IF EXISTS tname2;
CREATE TABLE tname2 as SELECT * FROM data WHERE group_name like f;
END;

Upvotes: 2

Views: 220

Answers (1)

John Woo
John Woo

Reputation: 263693

Since f is contains the value, a dynamic sql is needed so we can concatenate it with the original query,

DELIMITER $$

CREATE PROCEDURE createtableTest(IN tname2 varchar(20),IN f varchar(20))
BEGIN
    DROP TABLE IF EXISTS tname2;
    SET @sql = CONCAT('CREATE TABLE tname2 as SELECT * FROM data WHERE group_name like ''%',f,'%''');
    PREPARE stmt FROM @sql;
    EXECUTE stmt;
    DEALLOCATE PREPARE stmt;
END $$

DELIMITER ;

for example, the value of f is hello, the concatenated string will produce

CREATE TABLE tname2 as SELECT * FROM data WHERE group_name like '%hello%'

UPDATE

Aside from concatenation, you can also parameterized the value which is the best way, ex

DELIMITER $$

CREATE PROCEDURE createtableTest(IN tname2 varchar(20),IN f varchar(20))
BEGIN
    DROP TABLE IF EXISTS tname2;
    SET @sql = CONCAT('CREATE TABLE tname2 as SELECT * FROM data WHERE group_name like ?');
    PREPARE stmt FROM @sql;
    SET @val = CONCAT('%', f, '%');
    EXECUTE stmt USING @val;
    DEALLOCATE PREPARE stmt;
END $$

DELIMITER ;

Upvotes: 2

Related Questions