Cursors

SQL Server is very good at handling sets of data. For example, you can use a single UPDATE statement to update many rows of data. 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.

The basic syntax of a cursor is:
--------------------------------

----------------------------------------------------------------------------------
DECLARE @AuthorID char(11) DECLARE c1 CURSOR READ_ONLYFORSELECT au_idFROM authorsOPEN c1FETCH NEXT FROM c1INTO @AuthorIDWHILE @@FETCH_STATUS = 0BEGIN PRINT @AuthorID FETCH NEXT FROM c1 INTO @AuthorIDENDCLOSE c1DEALLOCATE 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 READ_ONLY clause is important in the code sample above. That dramatically improves the performance of the cursor.

In this example, I just print the contents of the variable. You can execute any type of statement you wish here. In a recent script I wrote I used a cursor to move through the rows in a table and call a stored procedure for each row passing it the primary key. Given that cursors are not very fast and calling a stored procedure for each row in a table is also very slow, my script was a resource hog. However, the stored procedure I was calling was written by the software vendor and was a very easy solution to my problem. In this case, I might have something like this:

EXEC spUpdateAuthor (@AuthorID)

instead of my Print statement. The CLOSE statement releases the row set and the DEALLOCATE statement releases the resources associated with a cursor.

If you are going to update the rows as you go through them, you can use the UPDATE clause when you declare a cursor. You'll also have to remove the READ_ONLY clause from above.

----------------------------------------------------------------------------
DECLARE c1 CURSOR FORSELECT au_id, au_lnameFROM authorsFOR UPDATE OF au_lname
----------------------------------------------------------------------------

You can code your UPDATE statement to update the current row in the cursor like this

---------------------------------
UPDATE authors
SET au_lname = UPPER(Smith)
WHERE CURRENT OF c1
----------------------------------





-------------------------------------------------------------------------
http://www.sqlteam.com/article/cursors-an-overview