How do you move over a cursor?
-
I read it
Syntax
@@FETCH_STATUS
Return Types
integer
Remarks
Because @@FETCH_STATUS is global to all cursors on a connection, use @@FETCH_STATUS carefully. After a FETCH statement is executed, the test for @@FETCH_STATUS must occur before any other FETCH statement is executed against another cursor. The value of @@FETCH_STATUS is undefined before any fetches have occurred on the connection.
For example, a user executes a FETCH statement from one cursor, and then calls a stored procedure that opens and processes the results from another cursor. When control is returned from the called stored procedure, @@FETCH_STATUS reflects the last FETCH executed in the stored procedure, not the FETCH statement executed before the stored procedure is called.
I always used @@fetch_status for moving over a cursor and i was shocked when i read it , so do you know a better way to move over a cursor???
thanks
-
Declare one or more variables to retain the status of @@FETCH_STATUS. One variable for each cursor, especially if you have nested cursors. Then capture the status of @@FETCH_STATUS immediately after the fetch.
declare @myfetchstatus1 ine
...
fetch ...
set @myfetchstatus1 = @@FETCHSTATUS
Then use @myfetchstatus1 to determine what should be done.
hth
-
What would be more interesting is to ask why you think you need a cursor? What are you trying to do?
--Jeff Moden
RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.Change is inevitable... Change for the better is not.
Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally)
Viewing 3 posts - 1 through 2 (of 2 total)
You must be logged in to reply to this topic. Login to reply