MySQL stored procedure, handling multiple cursors and query results
- by Tirithen
How can I use two cursors in the same routine? If I remove the second cursor declaration and fetch loop everthing works fine. The routine is used for adding a friend in my webapp. It takes the id of the current user and the email of the friend we want to add as a friend, then it checks if the email has a corresponding user id and if no friend relation exists it will create one. Any other routine solution than this one would be great as well.
DROP PROCEDURE IF EXISTS addNewFriend;
DELIMITER //
CREATE PROCEDURE addNewFriend(IN inUserId INT UNSIGNED, IN inFriendEmail VARCHAR(80))
BEGIN
DECLARE tempFriendId INT UNSIGNED DEFAULT 0;
DECLARE tempId INT UNSIGNED DEFAULT 0;
DECLARE done INT DEFAULT 0;
DECLARE cur CURSOR FOR
SELECT id FROM users WHERE email = inFriendEmail;
DECLARE CONTINUE HANDLER FOR NOT FOUND SET done = 1;
OPEN cur;
REPEAT
FETCH cur INTO tempFriendId;
UNTIL done = 1 END REPEAT;
CLOSE cur;
DECLARE cur CURSOR FOR
SELECT user_id FROM users_friends WHERE user_id = tempFriendId OR friend_id = tempFriendId;
DECLARE CONTINUE HANDLER FOR NOT FOUND SET done = 1;
OPEN cur;
REPEAT
FETCH cur INTO tempId;
UNTIL done = 1 END REPEAT;
CLOSE cur;
IF tempFriendId != 0 AND tempId != 0 THEN
INSERT INTO users_friends (user_id, friend_id) VALUES(inUserId, tempFriendId);
END IF;
SELECT tempFriendId as friendId;
END //
DELIMITER ;
