DELIMITER $$

USE `zmk`$$

DROP FUNCTION IF EXISTS `FGetListDetails`$$

CREATE DEFINER=`root`@`%` FUNCTION `FGetListDetails`(TIDDetail INT, NumberDetais DOUBLE) RETURNS INT(11)
BEGIN
    
  DECLARE CountDetais INT;    
    
  CALL PGetListDetails(TIDDetail, NumberDetais);
       
  SET CountDetais  = 0;
    
  RETURN CountDetais ;
 END$$

DELIMITER ;

DELIMITER $$

USE `zmk`$$

DROP PROCEDURE IF EXISTS `PGetListDetails`$$

CREATE DEFINER=`root`@`%` PROCEDURE `PGetListDetails`(TIDDetail INT, NumberDetais DOUBLE)
BEGIN
    
    DECLARE Done INT DEFAULT 0;    
    DECLARE CountDetais INT;
    DECLARE ExistPurchaserPrice INT;
    DECLARE PurchaserPrice, TNumberDetais DOUBLE;
    DECLARE IDMaterial INT;   
    
    DECLARE BankCursor CURSOR FOR SELECT 
    IMD.IDMaterial, (IMD.Number * NumberDetais) AS TNumberDetais 
    FROM ItemsMaterialsDetails IMD 
    WHERE IMD.IDDetail=TIDDetail;
    DECLARE CONTINUE HANDLER FOR SQLSTATE '02000' SET Done=1;
    
    SET CountDetais  = 0;
    SET ExistPurchaserPrice  = 0;
    SET PurchaserPrice  = 0;
    
    SET ExistPurchaserPrice = (SELECT COUNT(IPD.ID) FROM ItemsPriceDetails IPD WHERE IPD.IDDetail=TIDDetail);
    
    SET PurchaserPrice = (SELECT IF((SELECT COUNT(IPD.ID) FROM ItemsPriceDetails IPD WHERE IPD.IDDetail=TIDDetail)>0, 
    (SELECT IPD.Price FROM ItemsPriceDetails IPD WHERE IPD.IDDetail=TIDDetail ORDER BY IPD.DatePrice DESC LIMIT 1), 0)); 
     
    IF ExistPurchaserPrice>0 THEN
	INSERT INTO ListDetailsForReserve(IDDetail, Number, Price) 
        VALUES (TIDDetail, NumberDetais, PurchaserPrice);
    END IF;
    
    OPEN BankCursor;
	WHILE Done = 0 DO
	  FETCH BankCursor INTO IDMaterial, TNumberDetais;
	  CALL PGetListDetails(IDMaterial, TNumberDetais);
	END WHILE;
    CLOSE BankCursor; 
    
    
	   
  #  SET NumberHours = FORMAT(NumberHours, 2);
    
   # RETURN IFNULL(NumberHours, 0);
    END$$

DELIMITER ;

DROP TEMPORARY TABLE IF EXISTS ListDetailsForReserve ; 
CREATE TEMPORARY TABLE ListDetailsForReserve(IDDetail INT(11), Number DOUBLE NOT NULL DEFAULT "0", 
Price DOUBLE NOT NULL DEFAULT "0", KEY IDDetail (IDDetail) ) ;

SELECT FGetListDetails(6132, 2);

SELECT * FROM ListDetailsForReserve ;

Error Code: 1456
Recursive limit 255 (as set by the max_sp_recursion_depth variable) was exceeded for routine PGetListDetails

 CALL PGetListDetails(6132, 2);

Error Code: 1456
Recursive limit 255 (as set by the max_sp_recursion_depth variable) was exceeded for routine PGetListDetails