SQL Cursors

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.

Basic Synthax

2015-05-14_17h59_44

  • 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
Advertisements

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out /  Change )

Google+ photo

You are commenting using your Google+ account. Log Out /  Change )

Twitter picture

You are commenting using your Twitter account. Log Out /  Change )

Facebook photo

You are commenting using your Facebook account. Log Out /  Change )

w

Connecting to %s