Can I reset cursor's position to the beginning?
As in the topic. Can I simply reset cursor‘s position to the beginning in Transact-SQL, so it can run again over the table? I want to reset it in the following context:
DECLARE @userID INT DECLARE user_cursor CURSOR FOR SELECT userID FROM users WHILE /* some condition */ BEGIN ... FETCH NEXT FROM user_cursor INTO @userID IF @@FETCH_STATUS = 0 BEGIN /*... here goes the reset of the cursor ...*/ END ... END
4 Solutions collect form web for “Can I reset cursor's position to the beginning?”
you need to declare your cursor as scroll, like this
declare c scroll cursor for (select statement);
then at any time for locating to the first just use the following
fetch first from c;
Another option that can be used that doesn’t force you to change the type of cursor is simply to close the cursor and re-open it:
CLOSE user_cursor OPEN user_cursor
scroll option will be cheaper in terms of resource usage, if that’s a change you can accept.
The data retrieved by the cursor will not change.
Defines a cursor that makes a temporary copy of the data to be used by the cursor. All requests to the cursor are answered from this temporary table in tempdb; therefore, modifications made to base tables are not reflected in the data returned by fetches made to this cursor, and this cursor does not allow modifications.
Use cursor loops and its taken care of for you…
cursor c_something IS select * from somewhere BEGIN for some_row in c_something LOOP -- do stuff with some_row.COLUMN; END LOOP; -- this closes the cursor for you when it has gone all the way through -- do other stuff for some_row in c_something LOOP -- opens the cursor again from the top -- do stuff with some_row.COLUMN; END LOOP; END;