?
Solved

Irregular behavior of @@Fetch_Status

Posted on 2007-10-19
1
Medium Priority
?
2,135 Views
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
)
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
Comment
Question by:Epurchase
1 Comment
 

Accepted Solution

by:
jtaylor78 earned 250 total points
ID: 20111672
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

Featured Post

 [eBook] Windows Nano Server

Download this FREE eBook and learn all you need to get started with Windows Nano Server, including deployment options, remote management
and troubleshooting tips and tricks

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

This article shows gives you an overview on SQL Server 2016 row level security. You will also get to know the usages of row-level-security and how it works
In part one, we reviewed the prerequisites required for installing SQL Server vNext. In this part we will explore how to install Microsoft's SQL Server on Ubuntu 16.04.
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.
Viewers will learn how to use the SELECT statement in SQL to return specific rows and columns, with various degrees of sorting and limits in place.
Suggested Courses

864 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