Can I reset cursor's position to the beginning?

sql-server tsql database-cursor
Oskar Szura picture Oskar Szura · Jun 26, 2013 · Viewed 34.1k times · Source

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

Answer

user2511414 picture user2511414 · Jun 26, 2013

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;

Related questions

What is an alternative to cursors for sql looping?

Using SQL 2005 / 2008 I have to use a forward cursor, but I don't want to suffer poor performance. Is there a faster way I can loop without using cursors?

Read More
SQL cursor fetch status meaning

I don't understand the meaning of following lines: WHILE 1 = 1 BEGIN FETCH NEXT FROM SomeCursor INTO @SomeId, @SomeOtherColumn IF @@FETCH_STATUS <> 0 BREAK What are the meanings of while 1=1? and if fetch status is different than 0?

Read More
How do I UPDATE from a SELECT in SQL Server?

In SQL Server, it is possible to insert rows into a table with an INSERT.. SELECT statement: INSERT INTO Table (col1, col2, col3) SELECT col1, col2, col3 FROM other_table WHERE sql = 'cool' Is it also possible to update a …

Read More