Cursor let you move through rows one at a time and perform processing on each row. There are times when you want to loop through a series of rows a perform processing for each row. In this case you can use a cursor.

Please note that cursors are the SLOWEST way to access data inside SQL Server. The should only be used when you truly need to access one row at a time. The only reason I can think of for that is to call a stored procedure on each row.  In the Cursor Performance article I discovered that cursors are over thirty times slower than set based alternatives.

The basic syntax

DECLARE @AuthorID char(11)

DECLARE c1 CURSOR READ_ONLY

FOR

SELECT au_id

FROM authors

OPEN c1

FETCH NEXT FROM c1

INTO @AuthorID

WHILE @@FETCH_STATUS = 0

BEGIN

PRINT @AuthorID

FETCH NEXT FROM c1

INTO @AuthorID

END

CLOSE c1

DEALLOCATE c1

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 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.

The CLOSE statement releases the row set and the DEALLOCATE statement releases the resources associated with a cursor.

My Reference is: MCTS Book SQL Server 2005