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?

Akina · Accepted Answer · 2020-01-24 09:41:22Z

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 :(

edited Jan 24, 2020 at 9:41

answered Jan 24, 2020 at 6:04

Akina

43.2k6 gold badges17 silver badges30 bronze badges

Sign up to request clarification or add additional context in comments.

4 Comments

Dino Over a year ago

Oh my word, i was complicating my life beyond belief. Thank you for your reply.

Akina Over a year ago

@Dino I'll try to simplify your life a little more... see update.

Dino Over a year ago

Akina You are a star, can I ask one more question? I have add a 'total' filed to the user rank where total = passed_test + has_picture + etc. Can I do that in the SELECT above or do i need to create an UPDATE user_rank after the previous statements?

Akina Over a year ago

@Dino Nothing prevents. You may add any field to the table structure and proper field or expression to the SELECT clause. But if its value can be calculated from another values stored in another fields no reason to create a static field. Simply create calculated column total INT AS (passed_test + has_picture + ...) STORED and add it into the structure. Additional expresion in SELECT clause not needed for it.

Collectives™ on Stack Overflow

mysql calling a function inside a stored procedure using phpmyadmin

1 Answer 1

UPDATE

4 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

UPDATE

4 Comments

Your Answer

Sign up or log in

Post as a guest

Related