Tell me, is it possible to force the cursor to return immediately to the first record after completion and to perform again a full pass from the beginning to the end?
|
1 answer
This can be done using the FETCH FIRST command. To do this, the cursor must be declared with the SCROLL option.
Example:
declare @table table (ID int); insert into @table values (1), (2), (3), (4), (5); declare cs cursor local scroll for select ID from @table order by checksum(newid()); declare @id int; open cs; declare @pass int = 1, @maxPass int = 2; while 1 = 1 begin fetch next from cs into @id; if @@fetch_status != 0 begin if @pass < @maxPass begin set @pass += 1; -- перемещаемся в начало, если нужен ещё один проход: fetch first from cs into @id; -- вариант, если нужно переоткрыть курсор: --close cs; --open cs; --fetch next from cs into @id; end; else -- иначе завершение break; end; print @id; end; close cs; deallocate cs; Instead of moving to the beginning, you can also rediscover the cursor with a couple of CLOSE and OPEN commands, then the SCROLL option is not needed. In this case, however, it should be borne in mind that when the cursor is closed, locks that could be set using the cursor are released, which is not always desirable. Those. CLOSE + OPEN not equivalent to FETCH FIRST completely.
i-one i-one
6,876 ten 28
- And with CLOSe + OPEN requests for filling the cursor will be executed again? I have a tricky sort there that sorts the records randomly and I would like to receive a new set for each new cycle. - iluxa1810
- @ iluxa1810, yes, when the cursor is re-opened, the request will be re-executed anew, and if you randomly sort, the order will be new. When shifted to the beginning with
fetch firstorder is preserved, since The request will not be exceeded. - i-one
|