Programming in MS SQL Server 2012

@@FETCH_STATUS



@@FETCH_STATUS system variable is used to retrieves the status of the last cursor FETCH statement issued against any cursor currently opened by the connection.

"0" = FETCH statement was successful.
"-1" = FETCH statement failed or the row was beyond the result set.
"-2" = Row fetched is missing.

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.

The following example describes complete CURSOR concept.

-- Example 147 --

 

DECLARE @Emp_Code int

DECLARE @Emp_Name varchar(50)

DECLARE @Salary money

DECLARE @Dept_Code int

DECLARE @Dept_Name varchar(50)

 

DECLARE EmployeeList CURSOR FOR

SELECT

   EMP.Emp_Code,

   EMP.Emp_Name,

   EMP.Salary,

   EMP.Dept_Code,

   DEPT.Dept_Name FROM EMP

INNER JOIN DEPT

ON EMP.Dept_Code = DEPT.Dept_Code

WHERE EMP.Dept_Code IS NOT NULL

 

OPEN EmployeeList

 

FETCH NEXT FROM EmployeeList

INTO @Emp_Code, @Emp_Name, @Salary, @Dept_Code, @Dept_Name

 

WHILE @@FETCH_STATUS = 0

BEGIN

   SELECT @Emp_Code, @Emp_Name, @Salary, @Dept_Code, @Dept_Name

 

   FETCH NEXT FROM EmployeeList

      INTO @Emp_Code, @Emp_Name, @Salary, @Dept_Code, @Dept_Name

END

 

CLOSE EmployeeList

DEALLOCATE EmployeeList


Query Output Screen



* * * * *


Email Your Comment To AUTHOR