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