CODEWITHSUNDEEP
phpmysqlstored-procedures
Dharmendra Singh
Dharmendra Singh

Reputation: 1226

mysql in clause use procedure instead subquery

I have created a procedure which returns a single column of officeid

call officetree(15);

I need to get list of employee under officeid's return by officetree procedure

select * from master_employee where officeid in ( here i want put my officeids return from procedure)

Is this possible to achieve this if yes what is the syntax for that.

Inside the procedure

Below ofcid is parameter of procedure

select  `ofc_id`
 from (select * from master_office
     order by `ofc_parent_id`, `ofc_id`) master_office,
    (select @pv := ofcid) office
 where (find_in_set(`ofc_parent_id`, @pv) > 0 
and @pv := concat(@pv, ',', `ofc_id`)) or ofc_id=ofcid

Upvotes: 0

Views: 2698

Answers (2)

Paul Spiegel
Paul Spiegel

Reputation: 31772

I think there is no way to use a stored procedure result like a subquery.

Your alternatives:

  1. Use the statement from the procedure as subquery.
  2. Fetch the IDs from the SP in PHP and execute a second query with the fetched IDs. select * from master_employee where officeid in ( list of previously fetched IDs )
  3. Use a more powerfull design for the tree structure like "materialized path" or "transitive closure table"

Upvotes: 0

shawn
shawn

Reputation: 4363

No, AFAIK, you can not use a SP as sub query in MySQL.

Ref: Using a stored procedure as subquery

Ref: https://forums.mysql.com/read.php?10,556522,556538#msg-556538

Is it possible to call stored procs in MySQL 5.5 subqueries. No.

And for a suggestion, use stored procedure as few as possible (my ten-year experience tells me)

Upvotes: 1

Related Questions