Mysql/mariadb stored procedure set one variable value from selected row through join operation

Question

I have one MySql stored procedure i.e. selecting one row using left join. How can I set one value into OUT parameter from result set.

Database table which one I am using as:

INSERT INTO `user_pool` (`id`, `pool_id`, `user_id`, `parent_id`, `sponsor_id`, `amount`, `created_at`, `updated_at`) VALUES
(1, 1, 1, NULL, 1, '0.00', '2022-10-12 04:42:09', '2022-11-02 04:44:27'),
(2, 1, 2, 1, 1, '0.00', '2022-10-12 04:43:34', '2022-11-02 04:43:16'),
(3, 1, 3, 1, 1, '0.00', '2022-10-12 04:43:34', '2022-11-02 04:43:16'),
(4, 2, 4, 3, 1, '0.00', '2022-10-12 04:47:31', '2022-11-02 05:32:03'),
(5, 1, 5, 2, 1, '0.00', '2022-10-12 04:47:31', '2022-11-02 04:43:16'),
(6, 1, 6, 2, 1, '0.00', '2022-10-12 04:47:31', '2022-11-02 04:43:16'),
(7, 2, 7, 2, 1, '0.00', '2022-10-12 04:47:31', '2022-11-02 04:43:16'),
(8, 1, 8, 3, 1, '0.00', '2022-10-12 04:47:31', '2022-11-02 04:43:16'),
(9, 1, 9, 3, 1, '0.00', '2022-10-12 04:47:31', '2022-11-02 04:43:16'),
(10, 2, 10, 4, 1, '0.00', '2022-10-12 04:47:31', '2022-11-02 07:21:34'),
(11, 2, 11, 2, 1, '0.00', '2022-10-12 04:50:23', '2022-11-02 12:30:03'),
(12, 2, 12, 8, 1, '0.00', '2022-10-12 04:50:23', '2022-11-02 12:30:43'),
(13, 2, 13, 4, 1, '0.00', '2022-10-12 04:50:23', '2022-11-02 04:43:16'),
(14, 1, 14, 5, 1, '0.00', '2022-10-12 04:50:23', '2022-11-02 04:43:16'),
(15, 1, 15, 5, 1, '0.00', '2022-10-12 04:50:23', '2022-11-02 04:43:16'),
(16, 1, 16, 6, 1, '0.00', '2022-10-12 04:50:23', '2022-11-02 04:43:16'),
(17, 1, 17, 6, 1, '0.00', '2022-10-12 04:50:23', '2022-11-02 04:43:16'),
(18, 2, 18, 7, 1, '0.00', '2022-10-12 04:50:23', '2022-11-02 04:43:16'),
(19, 1, 19, 8, 1, '0.00', '2022-10-12 04:50:23', '2022-11-02 04:43:16'),
(20, 1, 20, 8, 1, '0.00', '2022-10-12 04:50:23', '2022-11-02 04:43:16'),
(21, 2, 21, 8, 1, '0.00', '2022-10-12 04:50:23', '2022-11-02 04:43:16'),
(22, 1, 22, 9, 1, '0.00', '2022-10-12 04:50:23', '2022-11-02 04:43:16'),
(23, 1, 23, 9, 1, '0.00', '2022-10-12 04:50:23', '2022-11-02 04:43:16'),
(24, 2, 24, 10, 1, '0.00', '2022-10-12 04:50:23', '2022-11-02 12:23:34'),
(25, 2, 25, 10, 1, '0.00', '2022-10-12 04:50:23', '2022-11-02 12:23:40'),
(26, 2, 1, NULL, 1, '0.00', '2022-11-01 12:12:56', '2022-11-02 04:43:16'),
(27, 2, 2, 1, 1, '0.00', '2022-11-02 05:18:40', '2022-11-02 05:18:40'),
(28, 2, 3, 1, 1, '0.00', '2022-11-02 05:19:54', '2022-11-02 05:19:54'),
(29, 2, 8, 3, 1, '0.00', '2022-11-02 05:21:39', '2022-11-02 07:05:53');

--
-- Indexes for dumped tables
--

--
-- Indexes for table `user_pool`
--
ALTER TABLE `user_pool`
  ADD PRIMARY KEY (`id`),
  ADD UNIQUE KEY `user_pool_parent` (`user_id`,`pool_id`) USING BTREE;

--
-- AUTO_INCREMENT for dumped tables
--

--
-- AUTO_INCREMENT for table `user_pool`
--
ALTER TABLE `user_pool`
  MODIFY `id` bigint(20) UNSIGNED NOT NULL AUTO_INCREMENT, AUTO_INCREMENT=34;
COMMIT;

DELIMITER $$
CREATE DEFINER=`root`@`localhost` PROCEDURE `test_sp`(IN `xid` INT, IN `xpool_id` INT, IN `xtree` INT, OUT `xnode` INT)
BEGIN
      
      WITH RECURSIVE generation AS 
      ( SELECT parent_id, user_id FROM user_pool WHERE user_id=xid AND pool_id=xpool_id
      UNION ALL 
      SELECT 
      child.parent_id, 
      child.user_id
      FROM user_pool child 
      JOIN generation g ON g.user_id = child.parent_id WHERE child.pool_id=xpool_id) 
      
    (SELECT g1.user_id, g1.parent_id, COALESCE(g2.CountOfChild,0) as CountOfChild
FROM generation g1 LEFT JOIN
  (SELECT parent_id,COUNT(*) CountOfChild FROM generation GROUP BY parent_id) g2
  ON g1.user_id=g2.parent_id HAVING CountOfChild < xtree ORDER BY user_id, CountOfChild LIMIT 1);
 

  END$$
DELIMITER ;

Above stored procedure result is

user_id	parent_id	CountOfChild
5	3	2

I want to select/return user_id in OUT variable i.e. xnode. Something like SET user_id INTO xnode from but it's not working.

Note: I am using Version 10.4.24-MariaDB - mariadb.

score 1 · Accepted Answer · answered Nov 08 '22 at 10:33

Remember how many columns you are going to select with SELECT statement. Count of return variables must be equal to selected columns.

To assign value into variables use INTO statement at the end of SELECT statement like below code:

    (SELECT g1.user_id, g1.parent_id, COALESCE(g2.CountOfChild,0) as CountOfChild
FROM generation g1 LEFT JOIN
  (SELECT parent_id,COUNT(*) CountOfChild FROM generation GROUP BY parent_id) g2
  ON g1.user_id=g2.parent_id HAVING CountOfChild < xtree ORDER BY user_id, CountOfChild LIMIT 1) INTO xnode, @var2, @var3;

Mysql/mariadb stored procedure set one variable value from selected row through join operation

1 Answers1

Linked