CURSORS in SQL Server



When we use a Select statement in a stored procedure to get some result, we can get a collection of all the records which satisfy the given criteria. This result set can be returned back as a whole to the client application. We can’t move through each individual record one by one in this situation inside a stored procedure. Therefore, whenever we find some situation where we need to process each record individually we can use cursors.

In order to work with a cursor we need to perform some steps in the following order

  1. Declare  cursor
  2. Open cursor
  3. Fetch row from the cursor
  4. Process fetched row
  5. Close cursor
  6. Deallocate cursor

The DECLARE CURSOR statement defines the SELECT statement that forms the basis of the cursor. You can do just about anything here that you can do in a SELECT statement.
The OPEN statement executes the SELECT statement and populates the result set.
The FETCH statement returns a row from the result set into the variable. You can select multiple columns and return them into multiple variables. The variable @@FETCH_STATUS is used to determine if there are any more rows. It will contain 0 as long as there are more rows. We use a WHILE loop to move through each row of the result set.
CLOSE statement - Releases the current data and associated locks, but permits the cursor to be re-opened.
DEALLOCATE statement - Destroys the cursor.



The basic syntax of a cursor is:

DECLARE @AuthorID char(11)
       
DECLARE MyCursorName CURSOR READ_ONLY
      FOR
            SELECT au_id
            FROM authors -- My Table

OPEN MyCursorName

FETCH NEXT FROM MyCursorName
      INTO @AuthorID

      WHILE @@FETCH_STATUS = 0
      BEGIN

        PRINT @AuthorID

        FETCH NEXT FROM MyCursorName
                  INTO @AuthorID

      END

CLOSE MyCursorName

DEALLOCATE MyCursorName


Labels: