Irregular behavior of @@Fetch_Status

Posted on 2007-10-19
Last Modified: 2012-08-14
Hi all,
We are using stored procedure for data retrieval according to the parameter passed into it.
The issue is that SP returns data 1st time for the given parameter but not for 2nd time.

Following have been done to debug the issue:
Assigned Select statement in a variable and inserted into temp table. Same SQL statement inserted into temp table and returned same record on execution
PS: @@Fetch_Status = 0 on 1st calling of SP and 1 on 2nd call!

Because of this SP didnt return expected values 2nd time

Any help would be highly appreciated.

--Example Code

Create Procedure GetEmployeeName
      @JoinDate      DateTime

Declare @LastName varchar (100), @FirstName varchar(100),
@Designaation varchar (100)

Declare MyCursor Cursor FOR SELECT LastName, FirstName, Designaation FROM Employee WHERE [JoinDate]= @JoinDate

OPEN MyCursor

FETCH NEXT FROM MyCursor INTO @LastName, @FirstName, @Designaation

WHILE @@Fetch_Status = 0
      IF @Designaation = UPPER(Manager)
      EXEC CalculateSalary(1) -- A cursor is opened and closed accordingly
ELSE IF @Designaation = UPPER(Admin)
EXEC CalculateSalary(2) -- A cursor is opened and closed accordingly
SET &.
IF &.
FETCH NEXT FROM MyCursor INTO @LastName, @FirstName

CLOSE MyCursor
Question by:Epurchase
    1 Comment

    Accepted Solution

    I suspect your problem lies in the scope of the @@FETCH_STATUS variable.  It is global by default.  From the SQL Server Documentation at
    "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 the 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."

    What I do when nesting cursors is to read the value of @@FETCH_STATUS into local variables so that my loop is controlled by a local variable.

    Declare @FS1 int
    DECLARE Employee_Cursor CURSOR FOR
    SELECT EmployeeID, Title FROM AdventureWorks.HumanResources.Employee;
    OPEN Employee_Cursor;
    FETCH NEXT FROM Employee_Cursor;
    set @fs1 = @@FETCH_STATUS
    WHILE @FS1 = 0
          FETCH NEXT FROM Employee_Cursor;
          set @FS1 = @@FETCH_STATUS
    CLOSE Employee_Cursor;
    DEALLOCATE Employee_Cursor;


    Featured Post

    How your wiki can always stay up-to-date

    Quip doubles as a “living” wiki and a project management tool that evolves with your organization. As you finish projects in Quip, the work remains, easily accessible to all team members, new and old.
    - Increase transparency
    - Onboard new hires faster
    - Access from mobile/offline

    Join & Write a Comment

    Introduction SQL Server Integration Services can read XML files, that’s known by every BI developer.  (If you didn’t, don’t worry, I’m aiming this article at newcomers as well.) But how far can you go?  When does the XML Source component become …
    Everyone has problem when going to load data into Data warehouse (EDW). They all need to confirm that data quality is good but they don't no how to proceed. Microsoft has provided new task within SSIS 2008 called "Data Profiler Task". It solve th…
    Using examples as well as descriptions, and references to Books Online, show the documentation available for date manipulation functions and by using a select few of these functions, show how date based data can be manipulated with these functions.
    Via a live example, show how to backup a database, simulate a failure backup the tail of the database transaction log and perform the restore.

    746 members asked questions and received personalized solutions in the past 7 days.

    Join the community of 500,000 technology professionals and ask your questions.

    Join & Ask a Question

    Need Help in Real-Time?

    Connect with top rated Experts

    20 Experts available now in Live!

    Get 1:1 Help Now