x
login about faq Site discussion (meta-askssc)

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
more ▼

asked Oct 24 '09 at 11:04 AM in Default

Raja M gravatar image

Raja M
75 2 2 2

(comments are locked)
10|1200 characters needed characters left

1 answer: sort newest

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.

more ▼

answered Oct 24 '09 at 11:20 AM

Bob Hovious gravatar image

Bob Hovious
1.6k 5 6 9

(comments are locked)
10|1200 characters needed characters left
Your answer
toggle preview:

Up to 2 attachments (including images) can be used with a maximum of 524.3 kB each and 1.0 MB total.

Follow this question

By Email:

Once you sign in you will be able to subscribe for any updates here

By RSS:

Answers

Answers and Comments



Facebook logo Follow Ask SSC on Facebook
Find Ask SSC on Google+
linkedin logo Find us on LinkedIn

Topics:

x1835
x914
x54

asked: Oct 24 '09 at 11:04 AM

Seen: 1190 times

Last Updated: Oct 26 '09 at 06:35 AM

Copyright © 2002-2012 Simple Talk Publishing. All Rights Reserved. If you have any queries, please contact the site administrators.
Ask SQL Server Central is a community service provided by Red Gate.