MySQL Using stored procedure in select statement

Question

I have a stored procedure like so:

$connection->query('
   drop procedure if exists listing_count;
   create procedure listing_count(IN parent int(11))
   begin
    declare count1 int(11) default 0;
    declare count2 int(11) default 1;
    create temporary table ids as (select id from category where id=parent);
    while count1<>count2 do
     set count1=(select count(id) from ids);
     insert into ids(id) select id from category where id not in(select id from ids) and related in(select id from ids);     
     set count2=(select count(id) from ids);
    end while;
    (select count(*) from listing_category where category in(select id from ids));
   end');

$fetch=$connection->query('select *,listing_count(id) as listing_count from category')->fetchall(pdo::FETCH_UNIQUE|pdo::FETCH_ASSOC);

I would like to use my procedure like a function. So that listing_count gets the count so that I can use it. Do I need to create a separate function? Can a procedure get my count and return it?

Turning it into a function like so:

drop function if exists listing_count;
   create function listing_count(parent int(11)) returns int(11) deterministic
   begin
    declare count1 int(11) default 0;
    declare count2 int(11) default 1;
    create temporary table ids as (select id from category where id=parent);
    while count1<>count2 do
     set count1=(select count(id) from ids);
     insert into ids(id) select id from category where id not in(select id from ids) and related in(select id from ids);     
     set count2=(select count(id) from ids);
    end while;
    return (select count(*) from listing_category where category in(select id from ids));
   end

But this does not work. I am not very familiar with procedures vs functions but I assume that I can't add all the functionality into a function as I can in a procedure.

score 0 · Answer 1 · answered Apr 28 '18 at 10:41

0

I would like to use my procedure like a function.

You Can't Do That™.

I suggest you convert your sp to a stored function. That's a good idea in any case because it returns a single value. The way you have it now, it returns a one-column one-row result set. If it were a function it would work easily in every context you need it. In contrast, stored procedures returning result sets are not nearly as easy to use. For example, see this. How to use Table output from stored MYSQL Procedure

Or you could write a stored function to wrap your stored procedure and return the value. In my opinion that is an inferior solution, just because it has extra complexity.

answered Apr 28 '18 at 10:41

O. Jones

103,626
17
118
172

I tried to change it to a stored function but it stops working – Maciek Semik Apr 28 '18 at 11:43
There are situations where it is necessary to wrap a procedure in a function, for example when recursion is required ([MySQL stored functions are not allowed to recurse](https://dev.mysql.com/doc/refman/5.6/en/stored-routines-syntax.html)) – Nick Apr 28 '18 at 12:23
`DETERMINISTIC` means the function's result is based on its parameters and on nothing else, and always returns the same value for the same parameters. (Optimizers can exploit knowledge of deterministic functions.) Your function is based on parameters and the content of a table, so it is not deterministic. And, with respect, "it stops working" isn't very useful information. – O. Jones Apr 28 '18 at 12:39

MySQL Using stored procedure in select statement

1 Answers1