My first MySQL stored procedure!

I had to write a stored procedure (it is dexter related, believe it or not) and after much trial and error, here is the finished result. I post it here for my own reference more than anything in case I need to do it again some day:


DELIMITER $$
DROP PROCEDURE IF EXISTS FixIDsProc $$

CREATE PROCEDURE `FixIDsProc`()
BEGIN
DECLARE no_more_rows INT DEFAULT 0;
DECLARE idx INT DEFAULT 1;
DECLARE strName TEXT;
DECLARE my_cursor CURSOR FOR
SELECT `dn_name` FROM `discids` ORDER BY `discids`.`dn_name` ASC;
DECLARE CONTINUE HANDLER FOR NOT FOUND
SET no_more_rows = 1;

OPEN my_cursor;

FETCH my_cursor INTO strName;
REPEAT

UPDATE `discids` SET `id` = idx WHERE `dn_name` = strName;
SET idx = idx + 1;

FETCH my_cursor INTO strName;
UNTIL no_more_rows = 1
END REPEAT;
CLOSE my_cursor;

END $$

DELIMITER ;



And to view the stored procedure after it's been entered in, you do this:


SHOW CREATE PROCEDURE FixIDsProc\G


To see all the stored procedures on the DB, do this query:


SELECT ROUTINE_TYPE, ROUTINE_NAME, ROUTINE_SCHEMA FROM INFORMATION_SCHEMA.ROUTINES;