Nested cursors or inline procedure ?

[Alex Nu]

Hi,

This is the first time I use stored procedures. I have the following
2 procedures. I tried a few ways to include the cursor of procedure
"eljoin" inside procedure "calccorr", but it was giving me wrong results.

I'd greatly appreciate if someone could advice me how to accomplish that,
Time is critical for me, so I think if I avoid calling procedure eljoin so many
times I will gain speed.

Thanks

Alex

CREATE PROCEDURE eljoin( IN wordx CHAR(80), IN wordy CHAR(80), OUT Nij DOUBLE)
BEGIN
DECLARE cur CURSOR FOR SELECT COUNT(*) FROM backup AS a JOIN backup AS b USING(docnum) WHERE a.word=wordx AND b. word=wordy;

OPEN cur;
FETCH cur INTO Nij;
CLOSE cur;

END;

CREATE PROCEDURE calccorr(IN idi INT, IN wordx CHAR(80),IN Ni INT, IN nrep INT)
BEGIN
DECLARE idj INT(9);
DECLARE wordy CHAR(80);
DECLARE Nj INT(9);
DECLARE Nij DOUBLE;
DECLARE corre DOUBLE;
DECLARE done INT DEFAULT 0;
DECLARE cur CURSOR FOR SELECT * FROM wordfreq where id > idi;
DECLARE CONTINUE HANDLER FOR SQLSTATE '02000' SET done = 1;

OPEN cur;

WHILE nrep > 0 DO

FETCH cur INTO idj,wordy,Nj;

CALL eljoin(wordx,wordy,Nij);

SET corre = Nij / (Ni + Nj - Nij);

INSERT INTO correlations VALUES (wordx,wordy,corre);

SET nrep = nrep - 1;

END WHILE;

CLOSE cur;

END;