CODEWITHSUNDEEP
sql-servert-sqldatabase-cursor
Oskar Szura
Oskar Szura

Reputation: 2569

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

Upvotes: 36

Views: 45242

Answers (5)

user19425
user19425

Reputation: 155

I was searching for a similar answer in PostgreSQL.

MOVE FIRST goes to the end of the first row, i.e. running FETCH ALL after this will return all rows apart from first one. There doesn't seem to be a MOVE ZEROTH option. So this works:

MOVE FIRST c;
MOVE PRIOR c;
FETCH ALL c;

Hope this helps someone.

Upvotes: 0

matthew rapaport
matthew rapaport

Reputation: 11

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;

Upvotes: -5

Damien_The_Unbeliever
Damien_The_Unbeliever

Reputation: 239764

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

But the scroll option will be cheaper in terms of resource usage, if that's a change you can accept.

Upvotes: 25

bgs
bgs

Reputation: 3223

The data retrieved by the cursor will not change.

STATIC

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.

Upvotes: 1

user2511414
user2511414

Reputation:

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;

Upvotes: 44

Related Questions