Akshay Vaghasiya Akshay Vaghasiya - 1 year ago 102
MySQL Question

Hot to use concated variable into mysql query inside mysql stored procedure?

I have a concat string in mysql stored procedure. And I want to use that string in where clause in same stored procedure. But when I'm using it, I'm getting only first value as it is comma separated string (1,2,3). So I can't understand why this happens and what is solution for it. If anyone knows the reason and solution, then answer will be appreciated. Here is code of stored procedure.

DECLARE newids VARCHAR(255);

SELECT GROUP_CONCAT(id) INTO newids FROM temptblnewcustomers;

DROP TABLE IF EXISTS temptblcustomerspurchase;
CREATE TABLE temptblcustomerspurchase (b_id BIGINT(20), b_customer BIGINT(20), a_item VARCHAR(50));
INSERT INTO temptblcustomerspurchase(b_id, b_customer, a_item)
SELECT b.id AS b_id, b.customer_id AS b_customer, a.item AS a_item FROM purchases a JOIN (
SELECT id, customer_id, item FROM (
SELECT id, customer_id, item FROM purchases WHERE customer_id IN (newids) ORDER BY customer_id, id DESC) t
GROUP BY customer_id) b
ON a.item=b.item;

Here, temptblnewcustomers consists only one column with five rows having value like any id. And also in newids, I'm geting values like 2301,2302,2303. But when I'm using newids into WHERE IN (), It takes onle first id i.e, 2301. I think that there may be reason of newids datatype as it is VARCHAR and id of temptblnewcustomers is BIGINT. But I'm not sure that this can affect.

Answer Source

You can use Select query directly in WHERE IN Clause.

Recommended from our users: Dynamic Network Monitoring from WhatsUp Gold from IPSwitch. Free Download