I'm trying to write a procedure that when called will update columns in a table with provided information. The procedure will take in the column name to be updated and the value I want inserted into the column. However the table has some columns that are INT and some that are VARCHAR. Is there a way to do this with one procedure or will I need two separate procedures one for a VARCHAR column and one for an INT column?

The example in the title doesn't work and I didn't really expect it to. Just want to know if anyone else has encountered this and found a loophole in MySQL syntax.

CREATE PROCEDURE updateColumn(
    IN an_id,
    IN a_column_name VARCHAR,
    IN a_column_value VARCHAR || INT
)
BEGIN
    UPDATE foo
    SET a_column_name = a_column_value
    WHERE id = id
END $$

I'm trying to write a procedure that when called will update columns in a table with provided information. The procedure will take in the column name to be updated and the value I want inserted into the column. However the table has some columns that are INT and some that are VARCHAR. Is there a way to do this with one procedure or will I need two separate procedures one for a VARCHAR column and one for an INT column?

The example in the title doesn't work and I didn't really expect it to. Just want to know if anyone else has encountered this and found a loophole in MySQL syntax.

CREATE PROCEDURE updateColumn(
    IN an_id,
    IN a_column_name VARCHAR,
    IN a_column_value VARCHAR || INT
)
BEGIN
    UPDATE foo
    SET a_column_name = a_column_value
    WHERE id = id
END $$

• mysql
• stored-procedures

• 2 No, procedures don't support multiple types. – Barmar Commented Feb 7 at 16:04
• 2 SET a_column_name = a_column_value doesn't work, either. Column names are not evaluated as variables, that tries to set a column that's literally named a_column_name. – Barmar Commented Feb 7 at 16:05
• 2 You need to use dynamic SQL to set a column dynamically. You can have different procedures, updateColumnInt() and updateColumnChar(). – Barmar Commented Feb 7 at 16:06
• 2 WHERE id = id should be WHERE id = an_id – Barmar Commented Feb 7 at 16:08
• 1 You don't need BEGIN/END when the stored procedure is a single statement. – danblack Commented Feb 8 at 1:23

1 Answer 1

here's a trivial example using mysql shell.

DELIMITER $$

CREATE PROCEDURE updateColumn(
    IN idFld INT,IN tableName VARCHAR(128),
    IN colName VARCHAR(64),IN colValue VARCHAR(255)
)
BEGIN
    SET @sql = CONCAT('UPDATE ', tableName, ' SET ', colName, ' = ? WHERE id = ?');

    SET @val = colValue;
    SET @id = idFld;

    PREPARE stmt FROM @sql;
    EXECUTE stmt USING @val, @id;
    DEALLOCATE PREPARE stmt;
END $$

DELIMITER ;

mysql> describe emp;
+------------+---------------+------+-----+---------+----------------+
| Field      | Type          | Null | Key | Default | Extra          |
+------------+---------------+------+-----+---------+----------------+
| id         | int           | NO   | PRI | NULL    | auto_increment |
| first_name | varchar(50)   | NO   |     | NULL    |                |
| last_name  | varchar(50)   | NO   |     | NULL    |                |
| position   | varchar(100)  | YES  |     | NULL    |                |
| salary     | decimal(10,2) | YES  |     | NULL    |                |
| hire_date  | date          | YES  |     | NULL    |                |
+------------+---------------+------+-----+---------+----------------+
6 rows in set (0.00 sec)

mysql> select * from emp where ID =1;
+----+------------+-----------+---------------+----------+------------+
| id | first_name | last_name | position      | salary   | hire_date  |
+----+------------+-----------+---------------+----------+------------+
|  1 | Alice      | Johnson   | Lead Engineer | 82000.00 | 2022-01-15 |
+----+------------+-----------+---------------+----------+------------+
1 row in set (0.00 sec)

mysql> call updateColumn( 1, 'emp', 'position', 'Hardware Designer' );
Query OK, 0 rows affected (0.01 sec)

mysql> call updateColumn( 1, 'emp', 'salary', '92000.00' );
Query OK, 0 rows affected (0.02 sec)

mysql> select * from emp where ID =1;
+----+------------+-----------+-------------------+----------+------------+
| id | first_name | last_name | position          | salary   | hire_date  |
+----+------------+-----------+-------------------+----------+------------+
|  1 | Alice      | Johnson   | Hardware Designer | 92000.00 | 2022-01-15 |
+----+------------+-----------+-------------------+----------+------------+
1 row in set (0.00 sec)

hope this helps.