x

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, 2009 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 voted first

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, 2009 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.

New code box

There's a new way to format code on the site - the red speech bubble logo will automatically format T-SQL for you. The original code box is still there for XML, etc. More details here.

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

SQL Server Central

Need long-form SQL discussion? SQLserverCentral.com is the place.

Topics:

x1945
x985
x58

asked: Oct 24, 2009 at 11:04 AM

Seen: 1916 times

Last Updated: Oct 26, 2009 at 06:35 AM