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

* * * * *

Your Comment To AUTHOR