Friday, June 8, 2012

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

-- Close the cursor
CLOSE Employee_Cursor;
-- Must remove this cursor reference
DEALLOCATE Employee_Cursor;

No comments:

Post a Comment