Stored procedure

Discussion of open issues, suggestions and bugs regarding database management and administration tools for MySQL
Post Reply
mara392
Posts: 1
Joined: Mon 30 Apr 2018 09:45

Stored procedure

Post by mara392 » Mon 30 Apr 2018 09:55

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;

alexa
Devart Team
Posts: 2830
Joined: Fri 24 Jun 2011 14:17

Re: Stored procedure

Post by alexa » Thu 03 May 2018 09:55

We will review this post and will answer you as soon as possible.

alexa
Devart Team
Posts: 2830
Joined: Fri 24 Jun 2011 14:17

Re: Stored procedure

Post by alexa » Fri 11 May 2018 15:16

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.

Post Reply