I wrote a simple procedure to modify the whole column to a set of meaningless values. However, when I tried
call NewProc('tableName','colName')
it returned
1064 - You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near 'NULL' at line 1". The error may occur in the "set @sql0=CONCAT('select count(*) into ',@i,' from ',tab1);
but I have no clue why it's the problem.
CREATE DEFINER = root@% PROCEDURE NewProc (IN tn VARCHAR(20), IN mc VARCHAR(20))
BEGIN
DECLARE j INT;
DECLARE tab1 VARCHAR(20);
DECLARE col1 VARCHAR(20);
DECLARE stmt VARCHAR(1000);
SET tab1:=tn;
SET col1:=mc;
set @sql0=CONCAT('select count(*) into ',@i,' from ',tab1);
PREPARE stmt FROM @sql0;
EXECUTE stmt;
DEALLOCATE PREPARE stmt;
SET j=1;
WHILE j<@i DO
SET @sql1=CONCAT("update ",tab1," set ",col1,"='565656' where id=",j);
PREPARE stmt FROM @sql1;
EXECUTE stmt;
DEALLOCATE PREPARE stmt;
SET j=j+1;
END WHILE;
END;