Q on Cursors !

[fixthebug2003]

HI,
If I have two cursors to deal with in one Stored Procedure,
then how can i check the fetch_status for each cursor?
Which variable to use @@fetch_status??

thanks
Fixthebug2003

 

[bdreed35]

Post your proc. The general feeling is that cursors are bad and there often times is a way around them.

~Brian

 

[JohnDTampaBay]

@@FETCH_STATUS returns the status of the last cursor FETCH statement issued against any cursor currently opened by the connection.

The only way to know which cursor @@Fetch_Status refers to is to check the value immediately after fetching from either cursor.

--Angel
-----------------------------------
Every time I lose my mind, I wonder
if it's really worth finding.

 

[hmscott]

First, don't let Nigel read this post (Nigel, if you are reading this post, close it, close your eyes, say a short prayer and go on to the next post). :0~!

From BOL:

The @@FETCH_STATUS function reports the status of the last FETCH statement. The same information is recorded in the fetch_status column in the cursor returned by sp_describe_cursor. This status information should be used to determine the validity of the data returned by a FETCH statement prior to attempting any operation against that data. For more information, see @@FETCH_STATUS.

I would consider very carefully the need to have two cursors in one SP. There are alternate solutions (one might by using VB or VBScript.

best of luck,

hmscott

 

[fixthebug2003]

Why is that everybody is running away from cursors? Any specific reason..like performance?

 

[JohnDTampaBay]

Performance is probably the biggest reason. Set-based operations are almost always more efficient. Granted, sometimes there's no alternative but to loop through a recordset and process one row at a time. But 9 out of 10 times, a set-based operation can be written to produce the same results with better performance.

--Angel
-----------------------------------
Every time I lose my mind, I wonder
if it's really worth finding.