I’ve wanted to write a few stored procedures in MySQL for a while now, but I found it very fiddly and was unable to come up with one very quickly. Tonight I was determined to read up on MySQL documentation and get one going.
For my reference only, this stored procedure will loop through a recordset and update a single row in a table upon a single row lookup in another table.
DELIMITER // DROP PROCEDURE IF EXISTS sp_test // CREATE PROCEDURE sp_test () BEGIN DECLARE done INT DEFAULT FALSE; DECLARE myid INT; DECLARE cur1 CURSOR FOR SELECT id from table1 WHERE firstname IS NULL; DECLARE CONTINUE HANDLER FOR NOT FOUND SET done = TRUE; OPEN cur1; read_loop: LOOP IF done THEN LEAVE read_loop; END IF; FETCH cur1 INTO myid; UPDATE table1 SET firstname = (SELECT firstname from table2 WHERE id = myid) WHERE id = myid; END LOOP; close cur1; END; //
Once I got the procedure to compile, executing it was as simple as: