CODEWITHSUNDEEP
sql-servercursor
HHR
HHR

Reputation: 13

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

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.

Upvotes: 1

Views: 1428

Answers (2)

Yves M.
Yves M.

Reputation: 3318

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.

Upvotes: 0

Martin Smith
Martin Smith

Reputation: 453037

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

Upvotes: 4

Related Questions