SQL - How to use cursor

---------------------------------------------------------------------------------------------

--The following example uses @@FETCH_STATUS to control cursor activities in a WHILE loop.

--@@FETCH_STATUS returns the status of the last cursor FETCH statement issued against any cursor

--currently opened by the connection.

--  0 The Fetch statement was successful.

-- -1 The Fetch statement failed or the row was beyond the result set.

-- -2 The row fetched is missing.

---------------------------------------------------------------------------------------------

-- Declare a cursor first.

DECLARE Employee_Cursor CURSOR FOR -- For which dataset

SELECT TOP 10 BusinessEntityID,

       JobTitle

FROM AdventureWorks2008R2.HumanResources.Employee;

-- Need to open this cursor

OPEN Employee_Cursor;

-- Fetch next row from dataset

FETCH NEXT FROM Employee_Cursor;

-- Fetch status is 0, means fetched record successfully

WHILE @@FETCH_STATUS = 0

   BEGIN

-- Fetch next record from dataset

      FETCH NEXT FROM Employee_Cursor;

     

      -- We can see the last record shows the fetch status - -1

      PRINT 'Fetch Status - '+ CONVERT(VARCHAR(10),@@FETCH_STATUS)

   END;

-- Close the cursor

CLOSE Employee_Cursor;

-- Must remove this cursor reference

DEALLOCATE Employee_Cursor;

GO