AAAE_N AAAE_N - 4 months ago 18
SQL Question

How to reuse a cursor in tsql from the begining

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;


I used after this code :

WHILE @@FETCH_STATUS = 0

BEGIN

...
FETCH NEXT FROM c INTO @myVariable;
END;

CLOSE c;
DEALLOCATE c;


but it didnt work.

Answer

You can create a scroll cursor with read only and reset to the first record from the cursor when needed.

For example:

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;

Gets:

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.