Mysql Stored Function Call Stored Procedure

Question

I've a Stored Procedure that be need called for a function, but ever that I try I've the following error:

Dynamic SQL is not allowed in stored function or trigger

My function:

DROP FUNCTION IF EXISTS `clusbe`.`getLongitude`;
CREATE DEFINER=`root`@`localhost` FUNCTION `getLongitude`(`id` INT, `latitude` DOUBLE) RETURNS double
BEGIN
    DECLARE retorno DOUBLE;

    IF (latitude <> '') THEN
      set retorno = latitude;
     ELSE
      CALL proc_getLongitude(1, 0);
     END IF;

  RETURN retorno;
END;

My Stored Procedure:

DROP PROCEDURE IF EXISTS clusbe.proc_getLongitude;
CREATE DEFINER=`root`@`localhost` PROCEDURE `proc_getLongitude`(`id` INT, `longitude` DOUBLE)
BEGIN
    DECLARE retorno DOUBLE;

  IF (longitude <> '' AND longitude <> null) THEN
    set retorno = latitude;
   ELSE
    set @sql =  CONCAT("SELECT longitude FROM endereco as e, usuario as u WHERE e.id = u.endereco AND u.id = ", id);
    PREPARE stmt FROM @sql;    
    EXECUTE stmt ; 
    DEALLOCATE PREPARE stmt;
   END IF;   
END;

Some idea of the like solve it ?

Answer 1

As the error said you can't have dynamic SQL in stored function since your function calling the SP and SP executing the dynamic SQL. What you can do ass alternative is to store the dynamic SQL result in a temporary table and use that temp table in your function like

set @sql =  CONCAT("CREATE TEMPORARY TABLE table2 AS SELECT longitude FROM endereco as e, usuario as u WHERE e.id = u.endereco AND u.id = ", id);

In your function, fetch from temporary table instead of calling the procedure

   IF (latitude <> '') THEN
      set retorno = latitude;
     ELSE
      SELECT * FROM table2;
     END IF;