MySQL: Stored Procedure in WHERE clause

Question

So I want to pass dynamic value of param to stored procedure from parent query however it is producing error, here is the query:

SELECT *
FROM mytable
WHERE mytable.user_id IN (CALL getDownlines(mytable.user_id))

However when I run SP directly, it works fine:

CALL getDownlines(100)

Any ideas please ?

Here is SP for reference, not sure how to convert it into function:

DELIMITER $

DROP PROCEDURE IF EXISTS getDownlines$

CREATE PROCEDURE getDownlines(in_id INT)
BEGIN

    drop table if exists temp1;
    drop table if exists temp2;
    drop table if exists results; 

    create temporary table temp2 as (select id, upline_id from agents where upline_id = in_id); 
    create temporary table results as (select id, upline_id from temp2); 
    create temporary table temp1 (id int, upline_id int);

    while (select count(*) from temp2) do 

        insert into temp1 (id, upline_id)
        select a.id, upline_id 
        from agents a
        where a.upline_id in (select id from temp2) ;

        insert into results (id, upline_id)
        select distinct id, upline_id
        from temp1;

        delete from temp2;

        insert into temp2 (id, upline_id)
        select distinct id, upline_id
        from temp1;

        delete from temp1;
    end while;    

    select a.* 
    from results r
    join agents a
    on a.id = r.id;

    drop table if exists temp1;
    drop table if exists temp2;
    drop table if exists results; 

End $$  

DELIMITER ;

make it a function and you can call it

krishn Patel
– krishn Patel

2017-02-06 08:58:49 +00:00
Commented Feb 6, 2017 at 8:58 — krishn Patel
– krishn Patel, Commented Feb 6, 2017 at 8:58

juergen d · Accepted Answer · 2017-02-06 09:35:59Z

3

That is not possible to call a procedure from a select. Do it in 2 steps:

Call the procedure and store the result somewhere.
Run the select against the result of the procedure.

Or turn your procedure into a function.

edited Feb 6, 2017 at 9:35

answered Feb 6, 2017 at 8:58

juergen d

205k40 gold badges305 silver badges377 bronze badges

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

6 Comments

dev02 Over a year ago

Thanks but I want to pass dynamic parameter from parent query, can you provide some example of what exactly you mean ? thx

juergen d Over a year ago

You can either change the procedure to make the result for all user_ids or you could take the code of the procedure and put it directly into the select

dev02 Over a year ago

Cannot put procedure code directly in select cause it contains drop, insert, etc commands, please see updated question thanks

juergen d Over a year ago

Not sure that your procedure is doing but it has to be super slow, You should better put a select at the end of your procedure to get the result you desire.

dev02 Over a year ago

cannot understand your theory thanks anyways i will figure out myself

|

Collectives™ on Stack Overflow

MySQL: Stored Procedure in WHERE clause

1 Answer 1

6 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

6 Comments

Your Answer

Sign up or log in

Post as a guest

Related