Reset @@FETCH_STATUS stored procedure

Question

Hi, How can I reset the @@FETCH_STATUS variable or set it to 0 in a stored procedure?

Answer 1

You can reset it by reading a cursor which is not at the end of a table.

Answer 2

Another related question I have is can you bind FETCH_STATUS to a particular cursor?

Answer 3

You can't:

@@FETCH_STATUS (Transact-SQL)

Returns the status of the last cursor FETCH statement issued against any cursor currently opened by the connection.

So basically it's not bound to any cursor.

Answer 4

As Sklivvz wrote you cannot.

But I am wondering why do you want to reset it?

What is the real problem?

Answer 5

Typically you have a @@FETCH_STATUS immediately after a FETCH, so why would you want to reset it?

Try to store its result in a temporary variable if you do not evaluate immediately.

Answer 6

If you want to break cursor You can use BREAK
But this only only replace function from 0 to 1.

fetch next
While @@fetch_Status = 0
begin

if (my condition)
 break
fetch next ;

end