Tuesday, March 27, 2012

Cursor inside a cursor

I'm new to cursors, and I'm not sure what's wrong with this code, it run for ever and when I stop it I get cursor open errors

declare Q cursor for
select systudentid from satrans

declare @.id int

open Q
fetch next from Q into @.id
while @.@.fetch_status = 0
begin

declare c cursor for

Select
b.ssn,
SaTrans.SyStudentID,
satrans.date,
satrans.type,
SaTrans.SyCampusID,
Amount = Case SaTrans.Type
When 'P' Then SaTrans.Amount * -1
When 'C' Then SaTrans.Amount * -1
Else SaTrans.Amount END

From SaTrans , systudent b where satrans.systudentid = b.systudentid

and satrans.systudentid = @.id

declare @.arbalance money, @.type varchar, @.ssn varchar, @.amount money, @.systudentid int, @.transdate datetime, @.sycampusid int, @.before money

set @.arbalance = 0
open c
fetch next from c into @.ssn, @.systudentid, @.transdate, @.type, @.sycampusid, @.amount

while @.@.fetch_status = 0
begin

set @.arbalance = @.arbalance + @.amount
set @.before = @.arbalance -@.amount

insert c2000_utility1..tempbalhistory1
select @.systudentid systudentid, @.sycampusid sycampusid, @.transdate transdate, @.amount amount, @.type type, @.arbalance Arbalance, @.before BeforeBalance
where( convert (int,@.amount) <= -50
or @.amount * -1 > @.before * .02)
and @.type = 'P'

fetch next from c into @.ssn, @.systudentid, @.transdate, @.type, @.sycampusid, @.amount
end
close c
deallocate c
fetch next from Q into @.id

end
close Q
deallocate Q

select * from c2000_utility1..tempbalhistory1
truncate table c2000_utility1..tempbalhistory1Did you write this? Or was it given to you?

You don't need a cursor at all for this (although I'm having trouble trying to figure out what you're trying to do)...

Lose Cursor 1 altogethr.

Use the SELECT (which already has the table in it you want) as the Portion of the INSERT Statement...that's it...

Except for this

where( convert (int,@.amount) <= -50
or @.amount * -1 > @.before * .02)
and @.type = 'P'

Which I have no idea what you want.

I expect all you need to do is add that to the select

No comments:

Post a Comment