Cursor in SQL Server

In SQL procedures, a cursor make it possible to define a result set (a set of data rows) and performs complex logic on a row by row basis. By using the same mechanics, an SQL procedure can also define a result set and return it directly to the caller of the SQL procedure or to a client application.

A cursor can be viewed as a pointer to one row in a set of rows. The cursor can only reference one row at a time, but can move to other rows of the result setas needed.

To use cursors in SQL procedures, you need to do the followings:

• Declare a cursor that defines a result set.
• Open the cursor to establish the result set.
• Fetch the data into local variables as needed from the cursor, one row at atime.
• Close the cursor when done.

Example:

DECLARE @fName varchar(50), @lName varchar(50)
DECLARE cursorName CURSOR -- Declare cursor
LOCAL SCROLL STATIC

FOR

Select firstName, lastName FROM myTable

  OPEN cursorName -- open the cursor

     FETCH NEXT FROM cursorName
     INTO @fName, @lName
     PRINT @fName + ' ' + @lName -- print the name

     WHILE @@FETCH_STATUS = 0

        BEGIN

           FETCH NEXT FROM cursorName
           INTO @fName, @lName
           PRINT @fName + ' ' + @lName -- print the name

        END

  CLOSE cursorName -- close the cursor

DEALLOCATE cursorName -- Deallocate the cursor