I have a Stored Procedure
that takes three parameters, one of which is TEXT
and it should contain comma separated values with ids
, something like this -> '12345,54321,11111,22222'
, and this it inserts a row with data for each id in the list. Below is the Stored Procedure
:
DELIMITER //
-- Create Stored Procedure
CREATE PROCEDURE MyProcedure(
IN ItemUUID VARCHAR(255),
IN ReceiverIds TEXT,
IN ItemCreated VARCHAR(255)
)
BEGIN
DECLARE strLen INT DEFAULT 0;
DECLARE SubStrLen INT DEFAULT 0;
IF ReceiverIds IS NULL THEN
SET ReceiverIds = '';
END IF;
do_this:
LOOP
SET strLen = LENGTH(ReceiverIds);
INSERT INTO item_receiver (item_uuid, receiver_id, item_created)
VALUES (ItemUUID ,SUBSTRING_INDEX(ReceiverIds, ',', 1),ItemCreated);
SET SubStrLen = LENGTH(SUBSTRING_INDEX(ReceiverIds, ',', 1)) + 2;
SET ReceiverIds = MID(ReceiverIds, SubStrLen, strLen);
IF ReceiverIds = '' THEN
LEAVE do_this;
END IF;
END LOOP do_this;
END//
DELIMITER ;
To get comma separated values with ids
, something like this -> '12345,54321,11111,22222'
I execute subquery
, however, when I call this Stored Procedure
I get this error -> Error Code: 1242. Subquery returns more than 1 row
SET group_concat_max_len = 2048;
call MyProcedure('random_test_uuid',(
SELECT CAST(GROUP_CONCAT(receiver_id SEPARATOR ',') AS CHAR) AS receiver_ids FROM receiver
WHERE user_id LIKE (SELECT user_id FROM user WHERE user_name LIKE 'myName')
GROUP BY receiver_id ),
'2017-09-24 23:44:32');
The problem is the subquery. Remove the group by
:
SELECT CAST(GROUP_CONCAT(receiver_id SEPARATOR ',') AS CHAR) AS receiver_ids
FROM receiver
WHERE user_id LIKE (SELECT user_id FROM user WHERE user_name LIKE 'myName')
With the group by
, you are getting a separate row for each receiver_id
. The group_concat()
is not doing anything.
Also, the CAST()
is unnecessary. And this would typically be written as:
SELECT GROUP_CONCAT(r.receiver_id SEPARATOR ',') AS receiver_ids
FROM receiver r JOIN
user u
ON u.user_id = r.user_id
WHERE u.user_name LIKE 'myName';
If 'myName'
is not using wildcards, then =
is more appropriate than like
.
If receiver_id
is not unique in receiver
, then you might want to add distinct
to the group_concat()
.