MySQL Call procedure inside function for count

Question

Is it possible to use a procedure inside a function? For example, I would like to gather all my rows related to an id but I would also like to count the rows and use it in a select statement. This is not working:

  drop procedure if exists relatives;
   create procedure relatives(in parent int(11),out counted int(11))
   begin
    set counted=(select count(*) from category where related=parent);
   end;
   drop function if exists relatives_count;
   create function relatives_count(parent parent(11)) returns int(11)
   begin
    declare count int(11);
    call relatives(parent,counted);
    return counted;
   end;

So that I can use the count

select relatives_count(id) from category

This is just for curiosity purposes. It may look senseless since I can just call a single select query and get the same results but I want to know how I can use my procedure out variable in a function.

Answer 1

Yes, a MySQL FUNCTION can call a MySQL PROCEDURE.

But... the operations the procedure performs will be limited to the operations allowed by a function. (We can't use a procedure to workaround the limitations placed on a function.)

"is not working" is so nebulously vague as to be practically useless in debugging the issue. What exact behavior is being observed?

My suspicion is that the SQL statements shown are failing, because there is no override for the default statement delimiter.

Also, parent(11) is not a valid datatype.

Be aware that when an identifier for a column in a SQL statement in a MySQL stored program matches an identifier used for an argument or local variable, MySQL follows a rule about which (the column name or the variable) that is being referenced.

Best practice is to adopt a naming convention for arguments and local variables that do not match column names, and to qualify all column references with a table name or table alias.

Personally, I use a prefix for arguments and local variables (a for argument, l for local, followed by a datatype i for integer, d for date/datetime, n for decimal, ...

DELIMITER $$

DROP PROCEDURE IF EXISTS relatives$$

CREATE PROCEDURE relatives(IN ai_parent INT(11),OUT ai_counted INT(11))
BEGIN
  SELECT COUNT(*)
    INTO ai_counted 
    FROM category c
   WHERE c.related = ai_parent
  ;
END$$

DROP FUNCTION IF EXISTS relatives_count$$

CREATE FUNCTION relatives_count(ai_parent INT(11))
RETURNS INT(11)
BEGIN
   DECLARE li_counted INT(11);
   CALL relatives(ai_parent,li_counted);
   RETURN li_counted;
END$$

DELIMITER ;

---

Please identify the exact behavior you observe. Error message when creating the procedure? Error message when executing the function? Unexpected behavior. That's much more precise and informative than telling us something "is not working".