Updating of base table through keyset driven cursors is going into infinite loop

Question

question

Raja M asked Oct 24, '09

Updating of base table through keyset driven cursors is going into infinite loop

Hi every one,

Just look at the problem.

--Creating a table called TestTable 
--with two columns and with an unique
--constraint on one of them
CREATE TABLE TestTable
( 
  id INT UNIQUE,
  name VARCHAR(20)
)

--Inserting 3 rows.  
INSERT INTO TestTable VALUES(1,'Raja');
INSERT INTO TestTable VALUES(2,'sunny');
INSERT INTO TestTable VALUES(3,'Raghu');

-- Checking the table
SELECT * FROM TestTable

--Declaring a keyset driven cursor
Declare FirstCursor Cursor
GLOBAL
KEYSET
FOR SELECT * FROM TestTable

-- Opening a cursor. 
-- So query associated with cursor
-- will be executed and keyset will be
-- placed in worktable of tempdb.
-- you might know Keyset is column(s)
-- which are used to identify the rows in a table. 
-- Here that is id column.
OPEN FirstCursor

-- Declaring some variables
DECLARE @TempId INT, @TempName VARCHAR(20)
DECLARE @TempCount INT;

-- Fetch the first row.
FETCH NEXT FROM FirstCursor into @TempId,@TempName

SET @TempCount=10;

-- Here i would like to replace the id values with 10,11,12.
-- But this while is going endless. What is the reason for this?
WHILE @@FETCH_STATUS=0
BEGIN
update TestTable
SET id=@TempCount WHERE CURRENT OF FirstCursor ;
SET @TempCount=@TempCount+1;
FETCH FirstCursor FROM FirstCursor into @TempId,@TempName
END

CLOSE FirstCursor
DEALLOCATE FirstCursor

sql-server-2005 t-sql cursor

10 |1200

Attachments: Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.

Answer 1 · 2009-10-24T11:20:30Z

Bob Hovious answered Oct 24, '09

Try this

WHILE @@FETCH_STATUS=0 
  BEGIN 
    update TestTable 
    SET id=@TempCount WHERE CURRENT OF FirstCursor ; SET @TempCount=@TempCount+1; 
    -- FETCH FirstCursor FROM FirstCursor into @TempId,@TempName 
    FETCH NEXT FROM FirstCursor into @TempId,@TempName 
  END

Also, what version of SQL are you running? All you are doing is numbering rows, and using ROW_ID() or IDENTITY would give you much better performance than a cursor.

10 |1200

Attachments: Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.

question