• Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 2161
  • Last Modified:

Irregular behavior of @@Fetch_Status

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
)
AS

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
BEGIN
      IF @Designaation = UPPER(Manager)
BEGIN
      &.      
      EXEC CalculateSalary(1) -- A cursor is opened and closed accordingly
      &
END
ELSE IF @Designaation = UPPER(Admin)
BEGIN
      &
EXEC CalculateSalary(2) -- A cursor is opened and closed accordingly
&.
END
SET &.
SET &
IF &.
ELSE
&.
FETCH NEXT FROM MyCursor INTO @LastName, @FirstName
END

CLOSE MyCursor
DEALLOCATE MyCursor
0
Epurchase
Asked:
Epurchase
1 Solution
 
jtaylor78Commented:
I suspect your problem lies in the scope of the @@FETCH_STATUS variable.  It is global by default.  From the SQL Server Documentation at http://msdn2.microsoft.com/en-us/library/ms187308.aspx
"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
   BEGIN
      FETCH NEXT FROM Employee_Cursor;
      set @FS1 = @@FETCH_STATUS
   END;
CLOSE Employee_Cursor;
DEALLOCATE Employee_Cursor;
GO

0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

Join & Write a Comment

Featured Post

Cloud Class® Course: Microsoft Azure 2017

Azure has a changed a lot since it was originally introduce by adding new services and features. Do you know everything you need to about Azure? This course will teach you about the Azure App Service, monitoring and application insights, DevOps, and Team Services.

Tackle projects and never again get stuck behind a technical roadblock.
Join Now