Results 1 to 5 of 5
  1. #1
    Join Date
    Apr 2007
    Posts
    29

    Red face Unanswered: Infinita eLooping problem

    Hi frenz,
    im using ms sql server 2000.I have 2 tables namely [CLN_PATIENT_LEDGER],A and [CLN_WRT_PATLED],B and a SP called, SP_DEFN_PATLED.

    The role of this SP is to get the two parameters (from patient ID, To Patient Id)
    and then find records based on these range of patient IDs from the table A and then fetch into table B.I will use the table B to display in my record.

    The problem is when i passing the parameters to this SP but it is creating an infinite records into the table B thus my system will hang!
    i used a similar SP with two parameter (from Date , to Date) which have no problem in functioning.I just don't understand what is the problem here! Please find below are the info of my tables and SP.
    __________________________________________________ __________________

    SP :
    CREATE PROCEDURE SP_DEFN_PATLED (@fromPat as varchar(25), @toPat as varchar(25))
    AS
    declare
    @pat_id varchar(25),
    @txnNo as varchar(50),
    @LedDate as datetime,
    @debitAmt as float,
    @creditAmt as float,
    @desc as varchar(350),
    @enterBy as varchar(25)
    --------------------------------------


    DECLARE PATLED_LIST CURSOR LOCAL SCROLL STATIC FOR
    SELECT PATIENT_ID,TXN_NO,LED_DATE,DEBIT_AMT,CREDIT_AMT,PA T_LED_DESC,ENTRY_BY FROM CLN_PATIENT_LEDGER
    WHERE PATIENT_ID >= @fromPat AND PATIENT_ID <= @toPat

    OPEN PATLED_LIST

    DELETE FROM CLN_WRT_PATLED

    FETCH FIRST FROM PATLED_LIST INTO @pat_id ,@txnNo,@LedDate,@debitAmt,@creditAmt,@desc,@enter By
    WHILE (@@FETCH_STATUS=0)
    BEGIN


    INSERT INTO CLN_WRT_PATLED VALUES(@pat_id ,@txnNo,@LedDate,@debitAmt,@creditAmt,@desc,@enter By)


    FETCH FIRST FROM PATLED_LIST INTO @pat_id ,@txnNo,@LedDate,@debitAmt,@creditAmt,@desc,@enter By
    END --LOOP WAS SHUTDOWN HERE ALREADY


    CLOSE PATLED_LIST

    DEALLOCATE PATLED_LIST
    GO
    ===================i hv attached the script for table A and table B in the attachment!
    Attached Files Attached Files
    Thanks & Regards!
    dev
    !==============================================!
    Live your life to the fullest Whilst u can...=_="
    Alwayz Aim for the moon. If you miss, you may Hit a star....-_+~
    Where there's a will, There's a way....;^D
    !==============================================!

  2. #2
    Join Date
    Mar 2007
    Location
    Holmestrand, Norway
    Posts
    332
    It might not be because you are using FETCH FIRST instead of FETCH NEXT in the while loop?
    Ole Kristian Velstadbråten Bangås - Virinco - MSSQL.no - Facebook - Twitter

  3. #3
    Join Date
    May 2007
    Posts
    49
    It is because use of 'Fetch First' instead of 'Fetch Next'.

    I think using direct insert query is a better option than using cursor -

    DELETE FROM CLN_WRT_PATLED

    INSERT INTO CLN_WRT_PATLED
    SELECT PATIENT_ID,TXN_NO,LED_DATE,DEBIT_AMT,CREDIT_AMT,
    PA T_LED_DESC,ENTRY_BY FROM CLN_PATIENT_LEDGER
    WHERE PATIENT_ID >= @fromPat AND PATIENT_ID <= @toPat
    Mihir Mehendale
    DB Team @ Clarion
    Clarion Technologies
    (SEI CMMI Level 3)
    Pune, India
    www.clariontechnologies.co.in
    www.vEmployee.com

  4. #4
    Join Date
    Apr 2007
    Posts
    29
    Gossshhh.....How can i so ......haha! thnx roac i didn't notice that one as i was typing it too fast ! Thnx lot
    Thanks & Regards!
    dev
    !==============================================!
    Live your life to the fullest Whilst u can...=_="
    Alwayz Aim for the moon. If you miss, you may Hit a star....-_+~
    Where there's a will, There's a way....;^D
    !==============================================!

  5. #5
    Join Date
    Apr 2007
    Posts
    29
    Hi Mihirclarion,

    Thnx lot for your idea. It works as the same with the cursor.I gues i do not need to use the sursor here...hehehe!
    Thanks & Regards!
    dev
    !==============================================!
    Live your life to the fullest Whilst u can...=_="
    Alwayz Aim for the moon. If you miss, you may Hit a star....-_+~
    Where there's a will, There's a way....;^D
    !==============================================!

Posting Permissions

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