Hi All,

I Created two cursor and I want to use value fetched from 1st cursor in the second one here is my code


CREATE PROCEDURE `iems`.`Sp_Report` ($date1 datetime,
$date2 datetime,$deskcode int)
BEGIN

Declare $empcode varchar(50);
Declare $empname varchar(100);
Declare $storycode varchar(100);
Declare $heading text;
Declare $wordcount int;
Declare $submittime datetime;
Declare $approvedtime datetime;
Declare $Transfercode varchar(100);
Declare $edition varchar(100);
Declare $pageno int;
Declare l_loop_end Int default 0;
Declare CursorVar Cursor For select EMPLOYEECODE,Concat(firstname , ' ' , lastname) from employee where deskcode=$deskcode;

Declare CursorVar1 Cursor For Select Distinct storycode from storyinfo where author=$empcode and storydate between $date1 and $date2;

Declare continue handler for not found set l_loop_end =1;

Open CursorVar;
Open CursorVar1;


Repeat


FETCH CursorVar into $empcode,$empname;
FETCH CursorVar1 into $storycode;


Select heading,STORYDATE,fnTotalGetNumberOfWords(story)
into $heading,$submittime,$wordcount
from storyinfo where storycode=$storycode and
version=(select max(version) from storyinfo b where b.storycode=$storycode and b.author=$empcode);

Select $heading,$submittime,$wordcount;





Until l_loop_end End Repeat;


Close CursorVar;
Close CursorVar1;


Here in this procedure my first cursor "CursorVar" fetched $empcode which will be used by "CursorVar1" for fetching the data in where condition. On executing the procedure it always return Null value for $storycode. Kindly Guide me how i can achieve the same.

I must admit I wish I had your example of coding in cursors when I was arguing with someone on whether cursors were a good or bad thing. I've tried to decode what you're doing into normal SQL but no promises :

Thx for the reply. But for ur information its only 1/4 part of my code that i posted bcz I am facing problem in this part only.