Cursors are very useful for allowing a user to step through a result set of a query and performing an operation/task on every row.
For example – stepping through the result set of a Customers table and appending two columns like FirstName and Surname to a variable. The result of these operations is one row of comma separated values.
- The DECLARE CURSOR statement defines the SELECT statement that forms the basis for the cursor. READ_ONLY is important here as it dramatically improves the performance of the Cursor.
- The OPEN statement executes the SELECT and populates the result.
- The FETCH statement returns a row from the result set into the variable
- FETCH_STATUS is used to determine if there are any more rows. It will contain the value of 0 as long as there are more rows. The WHILE part of this statement moves through each row of the result set,
- In this WHILE loop we just PRINT the contents of @AuthorID, but in fact you can execute any type of SQL you wish here. For example you could use a Stored procedure here and create a new unique Identifier for all authors based on their @AuthorID. For updates, you will need to remove the READ_ONLY clause from the cursor declararion
- CLOSE releases the row set and DEALLOCATE release the resources associated with a cursor