using cursor for manipulation - SQL Server Forums

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

General SQL Server Forums

New to SQL Server Programming

using cursor for manipulation

Author

Topic

rajnidas
Yak Posting Veteran

97 Posts

Posted - 2014-10-17 : 07:33:23

ALTER procedure [dbo].[sp_debit_credit]

as begin

SET NOCOUNT ON

DECLARE @DEBIT_ID INT
DECLARE @CREDIT_ID INT
DECLARE @servicedamount decimal(18,2)
declare @bal_Serviced_Amt decimal(18,2)
DECLARE @transamount decimal(18,2)
DECLARE @newserviedamount decimal(18,2)
DECLARE @AccountNumber bigint
DECLARE @MYFLAG BIT

DECLARE cur_res CURSOR STATIC LOCAL

STATIC FOR

SELECT DEBIT.ID, DEBIT.ACCOUNT_NO
,DEBIT.SERVICED_AMT,
CREDIT.ID
,CREDIT.TRAN_AMT
,CREDIT.SERVICED_FLAG,
credit.Serviced_Amt

FROM TBL_INTEREST_DEBIT AS DEBIT

INNER JOIN TBL_CREDIT AS CREDIT ON DEBIT.ACCOUNT_NO = CREDIT.ACCOUNT_NO

WHERE CREDIT.TRANSACTION_VALUE_DATE >= DEBIT.TRANSACTION_VALUE_DATE and debit.Serviced_Flag =0 and credit.Serviced_Flag =0
ORDER BY CREDIT.id

OPEN cur_res

IF @@CURSOR_ROWS > 0
print @@CURSOR_ROWS

BEGIN

FETCH NEXT FROM cur_res INTO @DEBIT_ID ,@AccountNumber, @CREDIT_ID,@servicedamount ,@transamount,@MYFLAG,@bal_Serviced_Amt

WHILE (@@Fetch_status = 0)

BEGIN

declare @svcamt decimal(18,2)
set @svcamt=@servicedamount

declare @trnamt decimal(18,2)
set @trnamt=@transamount

declare @latestserviceamt decimal(18,2)
set @latestserviceamt =(@svcamt - @trnamt)

if sign(@latestserviceamt) < 0
begin
set @bal_Serviced_Amt = ABS(@latestserviceamt)

set @latestserviceamt=0
end

if (@latestserviceamt=0)
begin

update TBL_INTEREST_DEBIT set Serviced_Amt= @latestserviceamt,Serviced_Flag =1
where Account_No=@AccountNumber

update tbl_Credit set Serviced_Flag =1,Serviced_Amt = @bal_Serviced_Amt
where Account_No=@AccountNumber
end
else
begin

update TBL_INTEREST_DEBIT set Serviced_Amt= @latestserviceamt
where Account_No=@AccountNumber

update tbl_Credit set Serviced_Flag =1
where Account_No=@AccountNumber

FETCH NEXT FROM cur_res INTO @DEBIT_ID ,@AccountNumber, @CREDIT_ID,@servicedamount ,@transamount,@MYFLAG,@bal_Serviced_Amt

end

END

END

CLOSE cur_res

DEALLOCATE cur_res

SET NOCOUNT OFF

end

-- exec [sp_debit_credit]

table is like this
------------------------
DEBIT.ID, DEBIT.ACCOUNT_NO,DEBIT.SERVICED_AMT,CREDIT.ID ,CREDIT.TRAN_AMT,CREDIT.SERVICED_FLAG,credit.Serviced_Amt
1 45605086622 3983.00 0 6418 110.00 0 300000.00
1 45605086622 3983.00 0 6419 110.00 0 500000.00
1 45605086622 3983.00 0 6447 110.00 0 2800000.00
1 45605086622 3983.00 0 6510 110.00 0 3100303.00
1 45605086622 3983.00 0 6511 110.00 0 4176159.00
1 45605086622 3983.00 0 6550 110.00 0 3399547.18
1 45605086622 3983.00 0 6582 110.00 0 47264.00
1 45605086622 3983.00 0 6606 110.00 0 233704.00
1 45605086622 3983.00 0 6610 110.00 0 753894.00
1 45605086622 3983.00 0 6613 110.00 0 126026.00
1 45605086622 3983.00 0 6672 110.00 0 1454.00

i need result , i have to subtract (DEBIT.SERVICED_amt - CREDIT.TRAN_AMT) and set CREDIT.SERVICED_FLAG =1 ,
if DEBIT.SERVICED_amt = CREDIT.TRAN_AMT then set debit.SERVICED_FLAG =1
using with cursor

it will check row by row , i have written stored procedure , but flag is not updated .

i need help pls .

Thanks & regards

Rajnidas

gbritton
Master Smack Fu Yak Hacker

2780 Posts

Posted - 2014-10-17 : 09:35:10

Pity you're using a cursor for this. An alternate approach would be to set up triggers so that when one of the tables is updated, the other one is updated by the trigger. Then you can use set-based operations and ditch the cursor.

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources