Plz someone help me with this SP.It should perfect

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2000 Forums

SQL Server Development (2000)

Plz someone help me with this SP.It should perfect

Author

Topic

tommyboy
Starting Member

4 Posts

Posted - 2007-04-19 : 11:12:14

Hi there, could someone tell me what is the wrong with the below SP's CURSOR.I exec this sp through a table when update a filed value like this :
EXECUTE THROUGH TRIGGER
exec SP_ALOCATE_PAT_CREDIT @patid, @creditno, @totalamount, @userid, @sdesc ,@creDate,'C'

When i run execute this sp, I got this error :
ERROR:
Another user has modified the contents of this table or view;the database row you are modifying no longer exists in the database.Databse error: '[Microsoft][ODBC SQL Server Driver][SQL Server]A cursor with the name 'INVOICE_LIST' does not exist.[Microsoft][ODBC SQL Server driver][SQL Server]The statement has terminated.'

--===========STORED PROCEDURE=================

CREATE PROCEDURE SP_ALOCATE_PAT_CREDIT (@patid as varchar(25),@recno as varchar(25), @recamt as float, @userid as varchar(25), @sdesc as varchar(60), @recDate as datetime, @ledTxnType as char)
AS
declare

--@postingmode char(1),
@count integer,
@Extracount integer,
@Depcount integer,
@invoiceno varchar(25),
-- @refundno varchar(25),
@totalamount float,
@amountreceived float,

@send_led_date datetime,
--@sdesc varchar(60),
--@userid varchar(25),
@islno integer,
@highSNO integer,
@led_txn_no integer,
@ledtype char(1),
@billstatus char(1),
--@refundamt float,
@invamtRec float,
@invDepRef float,
@extraAllocated float,
@extraBalance float,
@bstatus as char,
@invoiceFlag as char,
@ledCnt as integer,
@turn as integer,
@ledE as float,
@Fsdesc as varchar(20)

select @ledCnt=count(*) from CLN_PATIENT_LEDGER where TXN_NO=@recno
if @ledTxnType='C'
set @Fsdesc='Fully Credited Against '
else
set @Fsdesc='Fully Receipted Against '

if @ledCnt=0
BeGiN
set @turn=0

select @Extracount=count(*) from CLN_EXTRA_AMT where TXN_NO=@recno
if @Extracount=0
INSERT INTO CLN_EXTRA_AMT (TXN_NO,TOTAL_AMT,ALLOCATED_AMT,STATUS,BALANCE,PATIENT_ID,LED_TYPE) VALUES(@recno,@recamt,0,'O',0,@patid,'E')

DECLARE INVOICE_LIST CURSOR GLOBAL SCROLL FOR
SELECT INVOICE_NO,TOTAL_AMT,AMT_RECEIVED,BILL_STATUS,POST_FLAG FROM CLN_INVOICE_NOTE
WHERE BILL_STATUS='O' and TXN_TYPE <>'S' and PATIENT_ID = @patid AND POST_FLAG='Y' ORDER BY BILL_DATE

OPEN INVOICE_LIST

FETCH FROM INVOICE_LIST INTO @invoiceno,@totalamount,@amountreceived,@bstatus,@invoiceFlag
WHILE (@@FETCH_STATUS<>0)
BEGIN

if @recamt<>0
Begin
if (@recamt = @totalamount) --and (@bstatus<>'C') and (@invoiceFlag='Y')
begin
update CLN_INVOICE_NOTE set AMT_RECEIVED=AMT_RECEIVED+@recamt where INVOICE_NO=@invoiceno
--begin
select @islno=isnull(MAX(CLN_PAT_LED_ID),0)+1 from CLN_PATIENT_LEDGER where PATIENT_ID=@patid
-- set @send_led_date = convert(datetime, convert(varchar(10) , getdate(),103),103)

insert into CLN_PATIENT_LEDGER(PATIENT_ID, CLN_PAT_LED_ID, TXN_NO, LED_DATE,
DEBIT_AMT, CREDIT_AMT , PAT_LED_DESC, ENTRY_BY, LED_TYPE)
VALUES(@patid, @islno, @recno, @recDate,0, @recamt, @Fsdesc + @invoiceno + ' - ' + @sdesc , @userid,@ledTxnType )
--end

set @recamt = 0
end
ELSE if (@recamt < @totalamount) --and (@bstatus<>'C') and (@invoiceFlag='Y')
begin
update CLN_INVOICE_NOTE set AMT_RECEIVED=AMT_RECEIVED+@recamt where INVOICE_NO=@invoiceno
--begin
select @islno=isnull(MAX(CLN_PAT_LED_ID),0)+1 from CLN_PATIENT_LEDGER where PATIENT_ID=@patid
--set @send_led_date = convert(datetime, convert(varchar(10) , getdate(),103),103)

insert into CLN_PATIENT_LEDGER(PATIENT_ID, CLN_PAT_LED_ID, TXN_NO, LED_DATE,
DEBIT_AMT, CREDIT_AMT , PAT_LED_DESC, ENTRY_BY, LED_TYPE)
VALUES(@patid, @islno, @recno, @recDate,0, @recamt, @Fsdesc + @invoiceno + ' - '+ @sdesc , @userid,@ledTxnType )
--end

set @recamt = 0
end
else if (@recamt > @totalamount) --and (@totalamount>0) and (@bstatus<>'C') and (@invoiceFlag='Y')
begin
select @invamtRec=(TOTAL_AMT-AMT_RECEIVED) from CLN_INVOICE_NOTE where INVOICE_NO=@invoiceno
update CLN_INVOICE_NOTE set AMT_RECEIVED=AMT_RECEIVED+@invamtRec where INVOICE_NO=@invoiceno

select @islno=isnull(MAX(CLN_PAT_LED_ID),0)+1 from CLN_PATIENT_LEDGER where PATIENT_ID=@patid
-- set @send_led_date = convert(datetime, convert(varchar(10) , getdate(),103),103)

insert into CLN_PATIENT_LEDGER(PATIENT_ID, CLN_PAT_LED_ID, TXN_NO, LED_DATE,
DEBIT_AMT, CREDIT_AMT , PAT_LED_DESC, ENTRY_BY, LED_TYPE)

VALUES(@patid, @islno, @recno, @recDate, 0,@invamtRec, @Fsdesc + @invoiceno + ' - ' +@sdesc , @userid,@ledTxnType)

set @recamt = @recamt- @invamtRec
end

--else if (@recamt > 0) --and (@bstatus='C') and (@invoiceFlag='Y')

else if @recamt=0
select @extraAllocated=ALLOCATED_AMT,@extraBalance=BALANCE from CLN_EXTRA_AMT where TXN_NO=@recno
if @extraAllocated=0 and @extraBalance=0
begin
update CLN_EXTRA_AMT set ALLOCATED_AMT=TOTAL_AMT,BALANCE=0 where TXN_NO=@recno
--set @recamt = 0
end

End

if (@recamt > 0)
begin
set @turn=@turn+1
update CLN_EXTRA_AMT set ALLOCATED_AMT=TOTAL_AMT-@recamt,BALANCE=@recamt where TXN_NO=@recno

select @turn=count(*) from CLN_PATIENT_LEDGER where TXN_NO=@recno and LED_TYPE='E'

if @turn=0
begin
select @islno=isnull(MAX(CLN_PAT_LED_ID),0)+1 from CLN_PATIENT_LEDGER where PATIENT_ID=@patid

insert into CLN_PATIENT_LEDGER(PATIENT_ID, CLN_PAT_LED_ID, TXN_NO, LED_DATE,
DEBIT_AMT, CREDIT_AMT , PAT_LED_DESC, ENTRY_BY, LED_TYPE)
VALUES(@patid, @islno, @recno, @recDate, 0,@recamt, 'Extra Amount Received ' + ' - ' +@sdesc , @userid,'E' )
end
else
BEGIN
select @ledE=BALANCE from CLN_EXTRA_AMT where TXN_NO=@recno
update CLN_PATIENT_LEDGER set CREDIT_AMT=@ledE where TXN_NO=@recno and LED_TYPE='E'
-- set @recamt = 0@recDate

END
end

FETCH INVOICE_LIST INTO @invoiceno,@totalamount,@amountreceived,@bstatus,@invoiceFlag
END

EnD
--CLOSE INVOICE_LIST
--PRINT 'All user-defined tables have been dropped from the database.'

DEALLOCATE INVOICE_LIST
GO
--=====================END OF SP=============

sshelper
Posting Yak Master

216 Posts

Posted - 2007-04-19 : 11:18:48

Since you are modifying the table used by your cursor, try making your cursor STATIC as follows:

DECLARE INVOICE_LIST CURSOR GLOBAL SCROLL STATIC FOR
SELECT INVOICE_NO,TOTAL_AMT,AMT_RECEIVED,BILL_STATUS,POST_FLAG FROM CLN_INVOICE_NOTE
WHERE BILL_STATUS='O' and TXN_TYPE <>'S' and PATIENT_ID = @patid AND POST_FLAG='Y' ORDER BY BILL_DATE

SQL Server Helper
http://www.sql-server-helper.com

tommyboy
Starting Member

4 Posts

Posted - 2007-04-19 : 11:30:04

quote:
Originally posted by sshelper

Since you are modifying the table used by your cursor, try making your cursor STATIC as follows:

DECLARE INVOICE_LIST CURSOR GLOBAL SCROLL STATIC FOR
SELECT INVOICE_NO,TOTAL_AMT,AMT_RECEIVED,BILL_STATUS,POST_FLAG FROM CLN_INVOICE_NOTE
WHERE BILL_STATUS='O' and TXN_TYPE <>'S' and PATIENT_ID = @patid AND POST_FLAG='Y' ORDER BY BILL_DATE

========================================
Hi ,
Thanx sshelper for your suggestion but i tried that too and still had problem :(
SQL Server Helper
http://www.sql-server-helper.com

sshelper
Posting Yak Master

216 Posts

Posted - 2007-04-19 : 11:41:43

How about adding the READ_ONLY option to it and see if it works. Also, do you really need it to be GLOBAL? Try making it LOCAL and see if it makes a difference.

SQL Server Helper
http://www.sql-server-helper.com

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources