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! :eek:
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! :)It might not be because you are using FETCH FIRST instead of FETCH NEXT in the while loop?|||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|||Gossshhh....How can i so .....haha! thnx roac i didn't notice that one as i was typing it too fast :p ! Thnx lot :rolleyes:|||Hi Mihirclarion,
:angel:
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!