Stored Procedure to pass column name as parameter

CREATE table TaskState (id int NOT NULL, TodoTask text ,ContinueTask text, CompleteTask text ,PRIMARY KEY (id)) ;

  CREATE PROCEDURE movedata(idp int,fromto  VARCHAR(20), tofor VARCHAR(20))
BEGIN
update TaskState set tofor= fromto where id=idp;
update TaskState set fromto='add' where id=idp;
END //

here i am moving data one column to another column and i want to varchar variable will have column name but it is failed how can i make query because i don’t want to create separate query for each transaction how can i create query for it

in database there are three + one ID column integer for pointing . in three column one column will have data and remaining null here i just moving data one field to another

so i need a query which will provide column name at runtime

Answer

CREATE PROCEDURE movedata(idp int,fromto  VARCHAR(20), tofor VARCHAR(20))
BEGIN
SET @sql := CONCAT( 'update TaskState set ', tofor, '=', fromto, ',', fromto, '='add' where id=', idp);
PREPARE stmt FROM @sql;
EXECUTE stmt;
DROP PREPARE stmt;
END

Leave a Reply

Your email address will not be published. Required fields are marked *