How to reuse a cursor in tsql from the beginning without changing its definition.
I saw this topic link but I dont know what to do after this
fetch first from c;
WHILE @@FETCH_STATUS = 0
FETCH NEXT FROM c INTO @myVariable;
You can create a scroll cursor with read only and reset to the first record from the cursor when needed.
DECLARE @T TABLE (Id int, Num int); insert into @T values (1,0),(2,0),(3,0); DECLARE @ID INT; DECLARE @Num INT; DECLARE @Counter INT = 0; DECLARE c SCROLL CURSOR FOR (SELECT Id, Num FROM @T) FOR READ ONLY; OPEN c; FETCH FIRST FROM c INTO @ID, @Num; WHILE @@FETCH_STATUS = 0 AND @Counter < 3 BEGIN SET @Counter = @Counter + 1; update @T set num = @Counter where Id = @ID; fetch first from c INTO @ID, @Num; FETCH NEXT FROM c INTO @ID, @Num; END; CLOSE c; select * from @T;
1 1 2 3 3 0
Just have to be aware that the values you get from the cursor don't change because you only retrieve the records once.
Since there's only 1 open and close.