Passing a field name as a parameter in a MySQL stored procedure

Question

I pass the table field name as a parameter to the stored procedure, but the stored procedure takes the field name as a value instead of the field name and throws an error.

For example, if I pass the value isEnabled through the FieldName parameter, Mysql throws an error, the unknown column isEnabled in the field list, which shows that mysql automatically adds a quote.

Here is an example of a stored procedure that I wrote.

CREATE `VSK_Comments_UpdateAction`(IN FieldName varchar(30),IN FieldValue tinyint,CID bigint) BEGIN Update comments Set FieldName=FieldValue WHERE commentid=CID; END; 

Is there a way so that I can correctly assign the field name dynamically.

Answer 1

You can use prepared instructions , for example -

 CREATE `VSK_Comments_UpdateAction`(IN FieldName varchar(30),IN FieldValue tinyint,CID bigint) BEGIN SET @query = CONCAT('Update comments Set ', FieldName, '=? WHERE commentid=?'); PREPARE stmt FROM @query; SET @FieldValue = FieldValue; SET @CID = CID; EXECUTE stmt USING @FieldValue, @CID; DEALLOCATE PREPARE stmt; END;