Results 1 to 3 of 3
  1. #1
    Join Date
    Mar 2009

    Unanswered: Cursor Related Issue

    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)

    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;


    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$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.

  2. #2
    Join Date
    Jun 2007
    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 :
    select e.EMPLOYEECODE,
    from   employee e, 
           storyinfo si
    where  e.deskcode=$deskcode
           and si.storydate between $date1 and $date2
           and si.version=(
                   select max(version) 
                   from storyinfo b 
                   where b.storycode=si.storycode and )

  3. #3
    Join Date
    Mar 2009
    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.

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts