Passing a column name as parameter to a stored procedure in mySQL

I’m creating some stored procedures to manage my DB. In particular, i want to create a stored procedore to edit a column, of a specific row, but i want to do it dinamically, passing the column name as an argument.

That’s what i want to do

CREATE PROCEDURE myDB.edit_myTable(
    IN key CHAR(16), 
    IN col VARCHAR(100), 
    new_value VARCHAR(200)
)
UPDATE myDB.myTable SET col = new_value

Using the parameter keyi find the specific row in myTablethat i want to edit, and i want to use the parameter col to edit just the column that i want.

I’ve already tried using CONCATE()or defining local variables, as i read on other topic, but i haven’t find a solution.

Any help?

Answer

You would need to use dynamic SQL :

DELIMITER //
CREATE PROCEDURE myDB.edit_myTable(
    IN key CHAR(16), 
    IN col VARCHAR(100), 
    new_value  VARCHAR(200)
)
BEGIN
    SET @s = CONCAT(
        'UPDATE myDB.myTable SET `', 
         col, '` = ', QUOTE(new_value),
         ' WHERE key = ', QUOTE(key)
    );
    PREPARE stmt FROM @s;
    EXECUTE stmt;
    DEALLOCATE PREPARE stmt;
END
//
DELIMITER;

Please note that, as commented by Paul Spiegel, using a variable for column name creates a risk of SQL injection. One solution for improve security would be to make sure that the input col does exists in the target table, using MySQL information schema :

DELIMITER //
CREATE PROCEDURE myDB.edit_myTable(
    IN key CHAR(16), 
    IN col VARCHAR(100), 
    new_value  VARCHAR(200)
)
BEGIN
    DECLARE col_exists INT;

    SELECT COUNT(*) INTO col_exists 
    FROM  information_schema.COLUMNS
    WHERE TABLENAME = 'mytable' AND COLUMN_NAME = col;

    IF (col_exists != 1) THEN
        SIGNAL SQLSTATE '45000'
        SET MESSAGE_TEXT = CONCAT('Column ', col, ' does not exist in table mytable');
    END IF;

    SET @s = CONCAT(
        'UPDATE myDB.myTable SET `', 
         col, '` = ', QUOTE(new_value),
         ' WHERE key = ', QUOTE(key)
    );
    PREPARE stmt FROM @s;
    EXECUTE stmt;
    DEALLOCATE PREPARE stmt;
END
//
DELIMITER;