Cursor for update result in loop...

  • Hi,

    I am trying to update a field in a table using cursor for update.

    so i have something like the following:

    however, in the declare cursor if i don't specify "order by recordid" which is the identity column, then it will update the first few (around 4000) record with correct value then just keep loop and update the same set of record over and over again.

    it was solved by adding the order by clause in the select statement.

    Can anyone explain to me what this is happing?

    thanks a lot.

    DECLARE

    @TCOUNTINT,

    @BARCODEVARCHAR(100),

    @NEWBARCODEVARCHAR(100)

    BEGIN

    DECLARE CFB CURSOR FOR

    SELECT BARCODE FROM FOLDERSBOX ORDER BY RECORDID FOR UPDATE OF BARCODE;

    OPEN CFB

    FETCH FROM CFB INTO @BARCODE

    WHILE @@FETCH_STATUS = 0

    BEGIN

    EXEC [dbo].[SPGetNextSequence] @TABLENAME = N'FOLDERSBOX', @FIELDNAME = N'BARCODE', @OUTSEQ = @NEWBARCODE OUTPUT

    --PRINT (@BARCODE+' ---> '+@NEWBARCODE);

    UPDATE FOLDERSBOX SET BARCODE = @NEWBARCODE WHERE CURRENT OF CFB;

    FETCH NEXT FROM CFB INTO @BARCODE

    END

    CLOSE CFB;

    DEALLOCATE CFB;

    END;

  • Ordinarily one uses cursors for normal database activity as a last resort. Hopefully you can simplify this a bit.

    Can you describe what you are trying to accomplish at a little higher level? Maybe avoiding using database or software terms?

  • DECLARE

    @TCOUNT INT,

    @BARCODE VARCHAR(100),

    @NEWBARCODE VARCHAR(100)

    BEGIN

    DECLARE CFB CURSOR FOR

    SELECT BARCODE FROM FOLDERSBOX ORDER BY RECORDID FOR UPDATE OF BARCODE;

    OPEN CFB

    FETCH FROM CFB INTO @BARCODE

    WHILE @@FETCH_STATUS = 0

    BEGIN

    EXEC [dbo].[SPGetNextSequence] @TABLENAME = N'FOLDERSBOX', @FIELDNAME = N'BARCODE', @OUTSEQ = @BARCODE OUTPUT

    UPDATE FOLDERSBOX SET BARCODE = @OUTSEQ WHERE CURRENT OF CFB;

    FETCH NEXT FROM CFB INTO @BARCODE

    END

    CLOSE CFB;

    DEALLOCATE CFB;

    END;

     


    N 56°04'39.16"
    E 12°55'05.25"

  • I believe you are encountering the loop due to the underlying data being updated by the cursor.  My suggestion would be to modify the code for a static cursor.

     

    DECLARE

     @TCOUNT    INT,

     @BARCODE   VARCHAR(100),

     @NEWBARCODE  VARCHAR(100),

     @PRIMARYKEY  INT

    BEGIN

     DECLARE CFB CURSOR STATIC FOR

     SELECT PRIMARYKEY, BARCODE FROM FOLDERSBOX ORDER BY RECORDID --FOR UPDATE OF BARCODE

     OPEN CFB

     WHILE 1=1 BEGIN

      FETCH NEXT

       FROM CFB INTO

        @PRIMARYKEY,

        @BARCODE

      If @@FETCH_STATUS <> 0 BREAK

      EXEC [dbo].[SPGetNextSequence] @TABLENAME = N'FOLDERSBOX', @FIELDNAME = N'BARCODE', @OUTSEQ = @BARCODE OUTPUT

      UPDATE

       FOLDERSBOX SET BARCODE = @OUTSEQ

       WHERE PRIMARYKEY = @primarykey-2

     END

     CLOSE CFB

     DEALLOCATE CFB

    END

     

    [font="Arial"]Clifton G. Collins III[/font]

  • We had the same problem where I work.  The REAL problem is the sequence table... the code called SPGetNextSequence can only return one sequence number (think GET NextID).  This will force the use of a cursor or While loop forever... and, it may take comparitively forever to run... unless...

    I need to see the code for SPGetNextSequence... I can fix this mess just like I did at work but I need to see your code to make sure it'll do what needs to be done.

    And, appologies about the word "mess" if you wrote the code... but we need to fix this or it will become the source of many long nights trying to figure out not only your current problem, but also where all the deadlocks will be coming from when you scale up.

    --Jeff Moden


    RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
    First step towards the paradigm shift of writing Set Based code:
    ________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

    Change is inevitable... Change for the better is not.


    Helpful Links:
    How to post code problems
    How to Post Performance Problems
    Create a Tally Function (fnTally)

  • Thanks,

    SPGetNextSequence return a value from a table then update it's value.

    everything worked fine once I put a ORDER BY a Identity Column in the declare cursor statement.

    so instead of

    DECLARE CFB CURSOR FOR

    SELECT BARCODE FROM FOLDERSBOX FOR UPDATE OF BARCODE;

    i put

    DECLARE CFB CURSOR FOR

    SELECT BARCODE FROM FOLDERSBOX ORDER BY RECORDID FOR UPDATE OF BARCODE;

    The purpose of this script is to update a unique field in a table based on another sequence table.

  • Okidoki

    --Jeff Moden


    RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
    First step towards the paradigm shift of writing Set Based code:
    ________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

    Change is inevitable... Change for the better is not.


    Helpful Links:
    How to post code problems
    How to Post Performance Problems
    Create a Tally Function (fnTally)

Viewing 7 posts - 1 through 6 (of 6 total)

You must be logged in to reply to this topic. Login to reply