SQLTeam.com | Weblogs | Forums

SQL cursor to while loop


#1

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

--end

select * from [test_table] order by PrjCode


#2
;

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

#3

can anyone add another option?


#4

why do you want a loop solution when you can use set based ?


#5

khtan, i will like an alternative as well. So if a set based will help i will be glad to have that solution too


#6

Set-based solution, for this type of problem, is typically 100x faster than loop based ...


#7

Kristen, please help me out with the set based solution .

I am standing by for a solution.

Khtan you can also help


#8

What's wrong with KHTans solution?


#9

what i have posted is a SET BASED solution.


#10

Ah .. thanks for answering my question :innocent: