Results 1 to 2 of 2
  1. #1
    Join Date
    Sep 2004
    Posts
    2

    Unanswered: 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..tempbalhistory1

  2. #2
    Join Date
    Nov 2002
    Location
    Jersey
    Posts
    10,322
    Did 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
    Code:
    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
    Brett
    8-)

    It's a Great Day for America everybody!

    dbforums Yak CorralRadio 'Rita
    dbForums Member List
    I'm Good Once as I ever was

    The physical order of data in a database has no meaning.

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •