Cursor problem...not picking up all data in the next fetch.

[CAFCrew]

HI All,

After a little while out of the game i am getting back into coding in SQL, however i seem a bit rusty..

This cursor is not picking up the new @Auth code when it picks up the next @idcol of the next record in the loop

can someone help me...i am sure its something really stupid but its driving me crazy!!

Many thanks in advance for your help


**************************CODE**************************
declare @idcol int
declare @auth int
declare @maxid int

set @idcol = 1
set @maxid = (select max(iddcol) from #tempa)

declare cur CURSOR

for

--declare @idcol int
--set @idcol = 57

select iddcol,trans from #tempa where iddcol = @idcol

open cur


fetch next from cur into @IDcol,@Auth

--while @lastidcol <> @idcol
WHILE @idcol <= @maxid

BEGIN
if @auth = 1
begin
update #tempa
set
TotalAmount = sumtotal,
progressflag = 1
from #tempa a
join
( select iddcol,mksystemid,mkaccountid,sum(txnvalue) + sum(cashback) + sum(gratuity) as sumtotal
from #tempa
WHERE iddcol = @idcol
group by iddcol, mksystemid, mkaccountid
) b on a.iddcol = b.iddcol
print 'goodbye'
end

else if @auth = 0
begin
update #tempa
set
TotalAmount = 0,
progressflag = 1
from #tempa a
where iddcol = @idcol
print 'hello'
print @idcol
print @auth
end

--set @lastidcol = @idcol
set @idcol = @idcol +1

END
fetch next from cur into @IDcol,@Auth
close cur
deallocate cur
**************************CODE**************************

 

[SQLSister]

First this is a job that should NOT be done with a cursor. Cursors are extremely bad for performance and should never be used when a set-based alternative is available. They are only for the very rare cases when set-based alternatives will not work.

Use a setbased update instead using the case statement to perform the if logic.

Next your existing logic doesn't make sense. if the following subselect has more than one value then only one of them is updating all records with the id.

select iddcol,mksystemid,mkaccountid,sum(txnvalue) + sum(cashback) + sum(gratuity) as sumtotal
                          from #tempa
                          WHERE    iddcol = @idcol
                          group by iddcol, mksystemid, mkaccountid

if there would only be one record why are you bothering with mksystemid,mkaccountid as they are not refernced anywhere else.

Give some sample data and sample expected results and we can better help you get the right code to do the job.


"NOTHING is more important in a database than integrity." ESquared

 

[CAFCrew]

Hi,

I am confused at your response, but thank you nonetheless for taking the time to look.

The code that you have highlighted is part of the set based update that is grouping by idcol etc (fair point, the other columns aren't really needed...shouldn't really be creating a problem tho)

the @auth is a flag that is set to 1 or 0 based on whether the transaction has been authorised or not. therefore the first "if" checks to see what part of the "IF" to execute. This then updates the total column. I wanted to do this as a cursor more for practise than anything else really..i know how much of a nightmare they are, and its useful to have a good template that can be used when i do need it. The "print" bits are so i can see what it is doing btw, and so i could try and work out what it was doing!

This shows that it is not picking up the new @Auth code for some reason when it picks the next Idcol. its always 0

sample data : i couldn't attach it..sorry! :s

idcol MkSystemid MkAccountid Txnvalue
1 1 4 10
2 2 3 10
3 3 2 10
4 4 1 10

Cashback Gratuity
10 0
1 3
2 0
1 0


TotalAmount TXNResult Trans Auth Message
0 AUTHORISED 1 REFUND ACCEPTED
0 AUTHORISED 1 Authorised
0 PENDING 0 NULL
0 AUTHORISED 1 Authorised


Progress Flag
0
0
0
0

Many thanks again for your help