mysql calling a function inside a stored procedure using phpmyadmin

Question

I have the following code that works:

BEGIN
  DECLARE done INT DEFAULT FALSE;
  DECLARE user_id int(11);
  DECLARE cur1 CURSOR FOR SELECT id FROM users;
  DECLARE CONTINUE HANDLER FOR NOT FOUND SET done = TRUE;

  # drop and re-create user_rank TABLE

  DROP TABLE IF EXISTS user_rank;

  CREATE TABLE `user_rank` (
    `id` int(11) UNSIGNED NOT NULL,
    `has_hobbies` int(3) DEFAULT 0,
    `passed_test` int(3) DEFAULT 0,
    `has_picture` int(3) DEFAULT 0,
    `won_a_job` int(3) DEFAULT 0,
    `is_prolancer` int(3) DEFAULT 0,
    `is_verified` int(3) DEFAULT 0,
    `has_portfolio` int(3) DEFAULT 0,
    `has_likes` int(3) DEFAULT 0,
    `has_disputes` int(3) DEFAULT 0,
    `has-earnings` int(3) DEFAULT 0,
    `has_feebacks` int(3) DEFAULT 0,
    `has_invitations` int(3) DEFAULT 0,
    `has_views` int(3) DEFAULT 0
) ENGINE=InnoDB DEFAULT CHARSET=utf8;

ALTER TABLE `user_rank`
    ADD PRIMARY KEY (`id`);

ALTER TABLE `user_rank`
    MODIFY `id` int(11) UNSIGNED NOT NULL AUTO_INCREMENT;

OPEN cur1;

read_loop: LOOP
FETCH cur1 INTO user_id;
IF done THEN
  LEAVE read_loop;
END IF;
INSERT INTO user_rank (id) values (user_id);
END LOOP;

CLOSE cur1;
END

it loops inside the table users using a cursor and copy all the user ids into the table user_rank. I have a function called "hasUserPassedTest" defined in my database that given a user id return 10 or 0. I would like to call the function in the loop above and insert it int the user_rank table but the following code does not work:

BEGIN
DECLARE done INT DEFAULT FALSE;
DECLARE user_id int(11);
DECLARE cur1 CURSOR FOR SELECT id FROM users;
DECLARE CONTINUE HANDLER FOR NOT FOUND SET done = TRUE;

# drop and re-create user_rank TABLE

DROP TABLE IF EXISTS user_rank;

CREATE TABLE `user_rank` (
    `id` int(11) UNSIGNED NOT NULL,
    `has_hobbies` int(3) DEFAULT 0,
    `passed_test` int(3) DEFAULT 0,
    `has_picture` int(3) DEFAULT 0,
    `won_a_job` int(3) DEFAULT 0,
    `is_prolancer` int(3) DEFAULT 0,
    `is_verified` int(3) DEFAULT 0,
    `has_portfolio` int(3) DEFAULT 0,
    `has_likes` int(3) DEFAULT 0,
    `has_disputes` int(3) DEFAULT 0,
    `has-earnings` int(3) DEFAULT 0,
    `has_feebacks` int(3) DEFAULT 0,
    `has_invitations` int(3) DEFAULT 0,
    `has_views` int(3) DEFAULT 0
) ENGINE=InnoDB DEFAULT CHARSET=utf8;

ALTER TABLE `user_rank`
    ADD PRIMARY KEY (`id`);

ALTER TABLE `user_rank`
    MODIFY `id` int(11) UNSIGNED NOT NULL AUTO_INCREMENT;

OPEN cur1;

read_loop: LOOP
    FETCH cur1 INTO user_id;
    IF done THEN
    LEAVE read_loop;
    END IF;
    INSERT INTO user_rank (id, has_hobbies) values (user_id, CALL 
    hasUserPassedTest(user_id));
 END LOOP;

  CLOSE cur1;
END

I am using CALL to invoke my function inside the stored procedure but does not work. How do I call my function within a stored procedure?

Answer 1

I am using CALL to invoke my function inside the stored procedure but does not work

CALL is not used to execute a function.

CALL Statement

The CALL statement invokes a stored procedure that was defined previously with CREATE PROCEDURE.

---

it loops inside the table users using a cursor and copy all the user ids into the table user_rank.

Why so complex? procedure, cursor, handler, loop... a simple query is enough:

INSERT INTO user_rank (id, has_hobbies) 
SELECT user_id, hasUserPassedTest(user_id)
FROM users;

---

UPDATE

The insertion may be combined with the table creation:

CREATE TABLE `user_rank` (
    `id` int(11) UNSIGNED NOT NULL,
    `has_hobbies` int(3) DEFAULT 0,
    `passed_test` int(3) DEFAULT 0,
    `has_picture` int(3) DEFAULT 0,
    `won_a_job` int(3) DEFAULT 0,
    `is_prolancer` int(3) DEFAULT 0,
    `is_verified` int(3) DEFAULT 0,
    `has_portfolio` int(3) DEFAULT 0,
    `has_likes` int(3) DEFAULT 0,
    `has_disputes` int(3) DEFAULT 0,
    `has-earnings` int(3) DEFAULT 0,
    `has_feebacks` int(3) DEFAULT 0,
    `has_invitations` int(3) DEFAULT 0,
    `has_views` int(3) DEFAULT 0
) ENGINE=InnoDB DEFAULT CHARSET=utf8
SELECT user_id AS id, hasUserPassedTest(user_id) AS has_hobbies
FROM users;

One subtlety - the fields inserted will be the last in the table structure. If the fields order makes sense then all fields must be mentioned in selection part:

CREATE TABLE `user_rank` (
    `id` int(11) UNSIGNED NOT NULL,
    `has_hobbies` int(3) DEFAULT 0,
    `passed_test` int(3) DEFAULT 0,
    `has_picture` int(3) DEFAULT 0,
    `won_a_job` int(3) DEFAULT 0,
    `is_prolancer` int(3) DEFAULT 0,
    `is_verified` int(3) DEFAULT 0,
    `has_portfolio` int(3) DEFAULT 0,
    `has_likes` int(3) DEFAULT 0,
    `has_disputes` int(3) DEFAULT 0,
    `has-earnings` int(3) DEFAULT 0,
    `has_feebacks` int(3) DEFAULT 0,
    `has_invitations` int(3) DEFAULT 0,
    `has_views` int(3) DEFAULT 0
) ENGINE=InnoDB DEFAULT CHARSET=utf8
SELECT user_id AS id, 
       hasUserPassedTest(user_id) AS has_hobbies
       0 AS `passed_test`,
       0 AS `has_picture`,
       0 AS `won_a_job`,
       0 AS `is_prolancer`,
       0 AS `is_verified`,
       0 AS `has_portfolio`,
       0 AS `has_likes`,
       0 AS `has_disputes`,
       0 AS `has-earnings`,
       0 AS `has_feebacks`,
       0 AS `has_invitations`,
       0 AS `has_views`
FROM users;

DROP TABLE stays a separate query :(