I want someone to convert my SQL Cursor below into a working while loop or any other alternative;
Declare Curupdate1 Cursor For Select distinct T0.[PrjCode],T0.[PrjName],
T1.[Account],T3.[AcctName],sum(T1.[Debit]) as D_BF,sum(T1.[Credit]) as C_BF,T4.CardName
From OPRJ T0 INNER JOIN JDT1 T1 ON T0.[PrjCode] = T1.[Project]
INNER JOIN OJDT T2 ON T1.[TransId] = T2.[TransId]
INNER JOIN OACT T3 ON T1.[Account] = T3.[AcctCode]
inner JOIN OCRD T4 ON T4.CardCode = T1.ShortName
where T1.[RefDate]< @Prjcode1
group by T0.[PrjCode],T0.[PrjName],T1.[Account],T3.[AcctName],T4.CardName
Declare @prjCode varchar(200),@prjName varchar(200),@Account varchar(200), @AcctName varchar(200),@D_BF money,@C_BF money,@Cardname nvarchar(200)
Open Curupdate1
Fetch Curupdate1 Into @prjCode,@prjName,@Account,@AcctName,@D_BF,@C_BF,@Cardname
While @@Fetch_Status=0
Begin
if @D_BF=''
begin
set @D_BF=0
set @C_BF=0
end
update [test_table] set Debit_BF =@D_BF,Credit_BF =@C_BF
where PrjCode =@prjCode and PrjName =@prjName and Account = @Account and AcctName = @AcctName
Fetch Curupdate1 Into @prjCode,@prjName,@Account,@AcctName,@D_BF,@C_BF,@Cardname
End
Close Curupdate1
Deallocate Curupdate1
;
WITH CTE
AS (
SELECT T0.[PrjCode],
T0.[PrjName],
T1.[Account],
T3.[AcctName],
sum(T1.[Debit]) AS D_BF,
sum(T1.[Credit]) AS C_BF,
T4.CardName
FROM OPRJ T0
INNER JOIN JDT1 T1
ON T0.[PrjCode] = T1.[Project]
INNER JOIN OJDT T2
ON T1.[TransId] = T2.[TransId]
INNER JOIN OACT T3
ON T1.[Account] = T3.[AcctCode]
INNER JOIN OCRD T4
ON T4.CardCode = T1.ShortName
WHERE T1.[RefDate] < @Prjcode1
GROUP BY T0.[PrjCode],
T0.[PrjName],
T1.[Account],
T3.[AcctName],
T4.CardName
)
UPDATE t
SET Debit_BF = CASE
WHEN @D_BF = ''
THEN 0
ELSE c.D_BF
END,
Credit_BF = CASE
WHEN @D_BG = ''
THEN 0
ELSE c.C_BF
END
FROM CTE c
INNER JOIN [test_table] t
ON c.PrjCode = t.PrjCode
AND c.PrjName = t.PrjName
AND c.Account = t.Account
AND c.AcctName = t.AcctName
