October 17, 2014 at 5:36 am
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
1456050866223983.0006418110.000300000.00
1456050866223983.0006419110.000500000.00
1456050866223983.0006447110.0002800000.00
1456050866223983.0006510110.0003100303.00
1456050866223983.0006511110.0004176159.00
1456050866223983.0006550110.0003399547.18
1456050866223983.0006582110.00047264.00
1456050866223983.0006606110.000233704.00
1456050866223983.0006610110.000753894.00
1456050866223983.0006613110.000126026.00
1456050866223983.0006672110.0001454.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
October 17, 2014 at 6:16 am
Isn't the "FETCH NEXT FROM cur_res" statement inside the WHILE loop in wrong place?
Check it out.
Igor Micev,My blog: www.igormicev.com
October 17, 2014 at 6:41 am
May I ask why you are using a cursor for this?
If you haven't even tried to resolve your issue, please don't expect the hard-working volunteers here to waste their time providing links to answers which you could easily have found yourself.
October 17, 2014 at 7:02 am
Phil Parkin (10/17/2014)
May I ask why you are using a cursor for this?
My question too. The solution can be easily replaced with set-based. Then everything is easier.
Igor Micev,My blog: www.igormicev.com
October 17, 2014 at 7:04 am
I'm with Phil. Don't fix this cursor, throw it away and create a proper set based solution. This is nothing more than 3 or 4 update statements.
_______________________________________________________________
Need help? Help us help you.
Read the article at http://www.sqlservercentral.com/articles/Best+Practices/61537/ for best practices on asking questions.
Need to split a string? Try Jeff Modens splitter http://www.sqlservercentral.com/articles/Tally+Table/72993/.
Cross Tabs and Pivots, Part 1 – Converting Rows to Columns - http://www.sqlservercentral.com/articles/T-SQL/63681/
Cross Tabs and Pivots, Part 2 - Dynamic Cross Tabs - http://www.sqlservercentral.com/articles/Crosstab/65048/
Understanding and Using APPLY (Part 1) - http://www.sqlservercentral.com/articles/APPLY/69953/
Understanding and Using APPLY (Part 2) - http://www.sqlservercentral.com/articles/APPLY/69954/
Viewing 5 posts - 1 through 4 (of 4 total)
You must be logged in to reply to this topic. Login to reply