Hello, I have a stored procedure with a cursor and call to another procedure, the result is entered into a table, but the procedure does not return the table, but multiple queries, ie. out parameters(Mtest).
Any help(:
DROP TEMPORARY TABLE IF EXISTS avtoreport;
CREATE TEMPORARY TABLE avtoreport (RN char(20),obekt char(200),km double(13,3),PRO_NT double(13,3),postR double(13,3)
,PROMr double(13,3),sumRaz double(13,3),nerazpredelqemi_ double(13,3));
OPEN AVTO_group;
BEGIN
DECLARE CONTINUE HANDLER FOR NOT FOUND
SET currAvto = 1;
A:
LOOP
FETCH AVTO_group INTO avtoname,obekt_,km_,avtoid;
IF currAvto = 1 THEN
LEAVE A;
END IF;
CALL MenagerReort_copy(avtoid,mydatao,mydatad,genfirm_p,Mtest);
SET sumRaz_=SUBSTRING_INDEX(SUBSTRING_INDEX(Mtest, ';', 20), ';', -1);
IF checkbox2=TRUE THEN
SET PROMr_=0;
SET postR_=sumRaz_;
ELSE
SET PROMr_=sumRaz_-SUBSTRING_INDEX(SUBSTRING_INDEX(Mtest, ';', 21), ';', -1);
SET postR_=SUBSTRING_INDEX(SUBSTRING_INDEX(Mtest, ';', 21), ';', -1);
END IF;
SET countallday=SUBSTRING_INDEX(SUBSTRING_INDEX(Mtest, ';', 22), ';', -1);
SET sumkm_=SUBSTRING_INDEX(SUBSTRING_INDEX(Mtest, ';', 10), ';', -1);
SET y = DATEDIFF(mydatad, mydatao);
SET iNumberOfDays=y+1;
SET razpredelqemi=CAST((postR_*countallday)/iNumberOfDays AS decimal(8,2));
IF checkbox2=TRUE THEN
SET procen=CAST((km_*100)/sumkm_ AS decimal(8,2));
SET PosTV=CAST((postR_*procen)/100 AS decimal(8,2));
SET PromV=CAST((PROMr_*procen)/100 AS decimal(8,2));
SET sumRaz_=PosTV;
IF CAST((postR_-(PosTV+PromV)) AS decimal(8,2))>0 THEN
SET nerazpredelqemi=CAST(postR_-sumRaz_ AS decimal(8,2));
ELSE
SET nerazpredelqemi=0;
END IF;
ELSE
SET procen=CAST((km_*100)/sumkm_ AS decimal(8,2));
SET PosTV=CAST((razpredelqemi*procen)/100 AS decimal(8,2));
SET PromV=CAST((PROMr_*procen)/100 AS decimal(8,2));
SET nerazpredelqemi=CAST(postR_-razpredelqemi AS decimal(8,2));
SET sumRaz_=CAST(PosTV+PromV AS DECIMAL);
END IF;
insert into avtoreport values (avtoname,obekt_,km_,procen,PosTV,PromV,sumRaz_,nerazpredelqemi);
SET Mtest=0;
END LOOP;
END;
CLOSE AVTO_group;
SELECT * FROM avtoreport;
DROP TEMPORARY TABLE IF EXISTS avtoreport;
Stored procedure
Re: Stored procedure
You could use the Code Debugger feature. Please perform the Compile for Debugging (Ctrl+Alt+F7) command for both the procedures and then run step by step debugging of the first procedure with F11. This may help locating the problem.