Results 1 to 2 of 2
  1. #1
    Join Date
    Jan 2004

    Unanswered: problem with cursor

    I'm simply trying to divide each close1 column by 20, then insert
    that value into the twentydayMA field. The query keeps on running
    without finishing, even if I'm only using a single page of data. What
    could be making this hang? To execute, I'm using exec tad778. I
    thought about using another cursor to to handle the return values,
    instead of trying to directly do so using the single cursor. Any


    CREATE procedure TAD778

    @close1 float,
    @20dayMA float,
    @date1 datetime,
    @recordcount int

    set nocount off

    /* Delcare the cursor that is going to find all data */

    Declare Cur_stocks Cursor
    For select close1, date1, twentydayMA
    from yhoo4

    Open Cur_stocks

    fetch Cur_stocks
    into @close1, @date1, @20dayMA

    select @recordcount = 0

    while @@fetch_status = 0
    Select "Copying record number" = @recordcount

    Select @recordcount = @recordcount + 1

    Select @20dayMA = @close1/20
    from yhoo4

    fetch Cur_stocks
    into @close1, @date1, @20dayMA
    insert into yhoo4(twentydayMA)
    close Cur_stocks
    deallocate Cur_stocks

    set nocount on

    return 0

  2. #2
    Join Date
    Jun 2003
    Provided Answers: 1
    You have no Fetch Next command. But beyond that there appears to be no reason for using a cursor to do this at all.

    This statement makes no sense:
    Select @20dayMA = @close1/20 from yhoo4 neither @20dayMA, @close1, or the constant 20 are even in yhoo4.

    What is @recordcount for? It doesn't appear to be used for anything.

    "I'm simply trying to divide each close1 column by 20, then insert
    that value into the twentydayMA field." No, you are trying to perform an UPDATE, not an INSERT. This statement...
    insert into yhoo4(twentydayMA) values(@20dayMA)
    ...inserts a new record into the table, (assuming relational integrity allows it, which it shouldn't if your table is correctly designed). It does not update any existing record.

    I think this one statement will do what you are trying to do:

    update yhoo4 set twentydayMA =@close1/20

    Welcome to the world of relational databases. You need to read up on database and table design, the Structured Query Language, and set-based operations, or you are going to be typing a lot of bloated code.
    If it's not practically useful, then it's practically useless.

    blindman "sqlblindman"

Posting Permissions

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