Results 1 to 8 of 8

Thread: Using Cursor

  1. #1
    Join Date
    Jul 2007
    Location
    Orlando,FL
    Posts
    35

    Unanswered: Using Cursor

    Using SQL2005. I need to create a cursor to update a field in a table with current date by Invoice Number. I get an endless loop and record is not updated. Where did I go wrong. David

    DECLARE

    @GRGR_ID CHAR(10),
    @SBSB_ID CHAR(10),
    @INVOICE_DUE_DATE DATETIME,
    @INVOICE_TOTAL_AMOUNT_DUE MONEY,
    @INVOICE_NUMBER CHAR(12),
    @PASS1_GENERATION_DATE DATETIME,
    @PASS1_TOTAL_AMOUNT_DUE MONEY,
    @PASS1_END_OF_GRACE DATETIME



    DECLARE DEQ CURSOR FOR
    SELECT
    GRGR_ID,
    SBSB_ID,
    INVOICE_DUE_DATE,
    INVOICE_TOTAL_AMOUNT_DUE,
    INVOICE_NUMBER,
    PASS1_GENERATION_DATE,
    PASS1_TOTAL_AMOUNT_DUE,
    PASS1_END_OF_GRACE

    FROM dbo.RPT_DELINQUENCY_TEST WHERE INVOICE_NUMBER = '080710019183'

    OPEN DEQ

    FETCH DEQ INTO
    @GRGR_ID,
    @SBSB_ID,
    @INVOICE_DUE_DATE,
    @INVOICE_TOTAL_AMOUNT_DUE,
    @INVOICE_NUMBER,
    @PASS1_GENERATION_DATE,
    @PASS1_TOTAL_AMOUNT_DUE,
    @PASS1_END_OF_GRACE

    WHILE @@FETCH_STATUS = 0
    BEGIN

    UPDATE dbo.RPT_DELINQUENCY_TEST SET @PASS1_GENERATION_DATE = GETDATE()
    WHERE SBSB_ID=@SBSB_ID AND GRGR_ID=@GRGR_ID AND INVOICE_NUMBER=@INVOICE_NUMBER

    END

    CLOSE DEQ
    DEALLOCATE DEQ

    Thank for your help.

  2. #2
    Join Date
    Jan 2004
    Location
    In a large office with bad lighting
    Posts
    1,040
    Where is your FETCH NEXT statement?

    -- This is all just a Figment of my Imagination --

  3. #3
    Join Date
    Jul 2007
    Location
    Orlando,FL
    Posts
    35
    Should I change my code from FETCH DEQ INTO to FETCH NEXT DEQ INTO or do I add a new line of code for the FETCH NEXT.

  4. #4
    Join Date
    Jan 2003
    Location
    Massachusetts
    Posts
    5,800
    Provided Answers: 11
    in the update statement itself, it looks like you are updating the variable @PASS1_GENERATION_DATE, instead of a column in the dbo.RPT_DELINQUENCY_TEST table.

  5. #5
    Join Date
    Jan 2003
    Location
    Massachusetts
    Posts
    5,800
    Provided Answers: 11
    You would add the FETCH NEXT line inside the while loop. Otherwise, @@FETCH_STATUS never gets updated.

    Any reason this is not being done as a straight update?

  6. #6
    Join Date
    Jul 2003
    Location
    San Antonio, TX
    Posts
    3,662
    Yeah, that's strange...
    UPDATE dbo.RPT_DELINQUENCY_TEST SET PASS1_GENERATION_DATE = GETDATE()
    WHERE SBSB_ID=@SBSB_ID AND GRGR_ID=@GRGR_ID AND INVOICE_NUMBER=@INVOICE_NUMBER
    "The data in a record depends on the Key to the record, the Whole Key, and
    nothing but the Key, so help me Codd."

  7. #7
    Join Date
    Jan 2003
    Location
    Massachusetts
    Posts
    5,800
    Provided Answers: 11
    Actually, it looks like that can even be simplified to take out the references to SBSB_ID and GRGR_ID, since the only input is @INVOICE_NUMBER.

  8. #8
    Join Date
    Jul 2003
    Location
    San Antonio, TX
    Posts
    3,662
    Yup, I missed that.
    "The data in a record depends on the Key to the record, the Whole Key, and
    nothing but the Key, so help me Codd."

Posting Permissions

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