Doesn't stop CURSOR loop when update result rows in SQL Server 2008

Question

I want to set DriverID column of Driver Table to 5000, 5001 ,....

For this purpose I write this script :

use WeighbridgeDB
GO

DECLARE @NewDriverID int;
SET @NewDriverID = 5000;

DECLARE Driver_Cursor CURSOR FOR 
SELECT DriverID FROM Driver
FOR UPDATE;

OPEN Driver_Cursor;

FETCH NEXT FROM Driver_Cursor

WHILE @@FETCH_STATUS = 0
BEGIN
    UPDATE Driver SET DriverID = @NewDriverID WHERE CURRENT OF Driver_Cursor;
    SET @NewDriverID += 1;
    FETCH NEXT FROM Driver_Cursor
END

CLOSE Driver_Cursor;
DEALLOCATE Driver_Cursor;
GO

But the While loop doesn't stop and the value of @@FETCH_STATUS is always 0. I think the Cursor rebuild itself since update occur on table or something.

How to correct this situation?

thanks.

I guess you are missing out INTO after fetch next from driver_cursor — Prashant Lakhlani
– Prashant Lakhlani, Commented Jul 9, 2012 at 11:41
@PrashantLakhlani - you don't need INTO. It means that the row will be sent to the client of the connection instead. And since the OP isn't trying to use any value from the current row, it's not obviously wrong. — Damien_The_Unbeliever
– Damien_The_Unbeliever, Commented Jul 9, 2012 at 12:51

Martin Smith · Accepted Answer · 2012-07-09 11:44:48Z

4

You don't need a cursor for this anyway (generally speaking try and avoid these in TSQL except for very few cases)

WITH T AS
(
SELECT *,
       ROW_NUMBER() OVER (ORDER BY DriverID) + 4999 AS NewDriverID
FROM Driver
)
UPDATE T SET DriverID = NewDriverID

answered Jul 9, 2012 at 11:44

Martin Smith

457k97 gold badges777 silver badges887 bronze badges

Sign up to request clarification or add additional context in comments.

Comments

Yves M. · Accepted Answer · 2012-07-09 11:53:34Z

0

You might do it like this:

UPDATE Driver
SET
    Driver.DriverID = d.NewDriverID 
FROM
(
    select 
        ROW_NUMBER() Over (ORDER BY DriverID) + 5000 AS NewDriverID,
        Driver.DriverID 
    from Driver 
) AS d
WHERE Driver.DriverID = p.DriverID

There is almost never a reason to use a cursor.

answered Jul 9, 2012 at 11:53

Yves M.

3,31816 silver badges12 bronze badges

Collectives™ on Stack Overflow

Doesn't stop CURSOR loop when update result rows in SQL Server 2008

2 Answers 2

Comments

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Related