Using Cursors in SQL Server
Part four of a five-part series of blogs

Cursors allow you to step through a set of data one record at a time. They’re not the quickest tool in SQL Server’s box, but they have their uses and this blog explains how they work.

  1. Cursors in SQL
  2. Declaring and Using Cursors in SQL
  3. Fetching Records with an SQL Cursor
  4. SQL Cursor Options (this blog)
  5. Updating Records Using a Cursor

This blog is part of our complete SQL tutorial; Wise Owl also run introductory and advanced SQL training courses.

Posted by Andrew Gould on 26 March 2013

You need a minimum screen resolution of about 700 pixels width to see our blogs. This is because they contain diagrams and tables which would not be viewable easily on a mobile phone or small laptop. Please use a larger tablet, notebook or desktop computer, or change your screen resolution settings.

SQL Cursor Options

In our example so far we haven't specified any additional options for our cursor, meaning that we're relying on using the default type of cursor in SQL. The extra options that you can specify for a cursor can affect which fetch options you can use, and what you can do with the data that your cursor will look at. This part of the blog series examines the different options for cursors that you can use in SQL.

The Scope of a Cursor

You can specify that your cursor will be either LOCAL or GLOBAL. This setting affects where you can refer to your cursor within the code you are writing, as described in the table below:

Setting Description
LOCAL This means that the cursor can only be referred to within the batch, stored procedure, or trigger in which it was created.
GLOBAL This means that the cursor can be referred to from anywhere within the connection in which it was created, including any stored procedure which is executed in that batch.

For a local cursor you must make sure you perform all of the actions within a single batch of statements. The code below would fail because a new batch is started between declaring the cursor and opening it:

USE Movies

--Begin a new batch

GO

DECLARE FilmCursor CURSOR LOCAL

FOR SELECT FilmID, FilmName, FilmReleaseDate FROM tblFilm

--Begin a new batch

GO

OPEN FilmCursor

The error message you would see is shown below:

Error message

The cursor is out of scope.

 

With a global cursor the code will work:

USE Movies

--Begin a new batch

GO

DECLARE FilmCursor CURSOR GLOBAL

FOR SELECT FilmID, FilmName, FilmReleaseDate FROM tblFilm

--Begin a new batch

GO

OPEN FilmCursor

If you don't specify the scope of a cursor it will use whichever type is specified by the Default Cursor option of the database. You can modify this option as described below:

  1. In the Object Explorer, right-click on the name of your database and choose Properties.
  1. In the dialog box which appears, select the Options category from the list on the left hand side.
Choosing options

Choose to view the Options page.

 
  1. Set the value of the Default Cursor option and click OK.
Setting default cursor

Choose from the two options in the drop down list.

Specifying the Scroll Type

The scroll type of a cursor determines which fetch statements you're allowed to use. The two settings you can use for the scroll type are described in the table below:

Scroll Type Description
FORWARD_ONLY The cursor can only move forwards through the set of records. You can only use the FETCH NEXT statement with this type of cursor.
SCROLL The cursor can move in any direction. You can use any of the fetch statements with this type of cursor.

If you don't specify which scroll type to use your cursor will default to FORWARD_ONLY. If you try to use an unsupported fetch statement with this type of cursor you will see an error message when you execute the code. The example below attempts to use the FETCH FIRST statement on a FORWARD_ONLY cursor:

DECLARE FilmCursor CURSOR FORWARD_ONLY

FOR SELECT FilmID, FilmName, FilmReleaseDate FROM tblFilm

OPEN FilmCursor

FETCH FIRST FROM FilmCursor

The error message that appears when this code is executed is shown below:

Error message

You need to use a SCROLL cursor if you want to use FETCH FIRST.

Specifying the Type of Record Set

This setting affects what you can do with the data returned by the cursor and also which fetch options are available. The four options that you can apply for this setting are described in the table below:

Record Set Type Description
STATIC This type of cursor makes a copy of the data it uses in the tempdb database. If any changes are made to the base data while this cursor is open you would not see those changes when you fetch records. This type of cursor does not allow modifications to data.
KEYSET When this type of cursor is opened it creates a table in the tempdb database which records the membership and order of the rows in the record set. This keyset table stores the unique identifiers of the records in the record set. If other users make changes to the non-key values in the record set this type of cursor will reflect the changes when you fetch records.
DYNAMIC This type of cursor always reflects all of the changes made to the data which populates the record set. You can't use the FETCH ABSOLUTE statement with this type of record set.
FAST_FORWARD This type of cursor can only move forwards through the record set using the FETCH NEXT statement. It has performance optimisations to make it work faster but it doesn't allow you to modify any data.

If you provide a value for this setting without specifying whether the cursor is FORWARD_ONLY or SCROLL this option will be set automatically according to the table below:

Specified record set type Default scroll type
FAST_FORWARD FORWARD_ONLY
STATIC, KEYSET or DYNAMIC SCROLL

Specifying the Lock Type

This setting affects what happens if you attempt to modify data returned by the cursor. If you don't specify the setting for this option, by default the cursor is allowed to modify data. The three values you can use for the lock type are described in the table below:

Lock type Description
READ_ONLY You can't make any changes to data using this type of cursor. If you set the record set type to FAST_FORWARD you will automatically create a READ_ONLY cursor.
SCROLL_LOCKS With this setting a record is locked as soon as the cursor scrolls to it meaning that it will be available for modifications. This ensures that updates or deletes performed by the cursor will always succeed. You cannot use this setting if you have also specified STATIC or FAST_FORWARD.
OPTIMISTIC Records are not locked as soon as they are read into the cursor. When you attempt to modify data with this cursor SQL Server checks to see if the record has been altered since it was read into the cursor - if so your update or delete will fail.

Combining Cursor Options

You can specify a scope, scroll type, record set type and lock type for a cursor in a single declaration.  The example below declares a global, forward only, static, read only cursor:

DECLARE FilmCursor CURSOR GLOBAL FORWARD_ONLY STATIC READ_ONLY

FOR SELECT FilmID, FilmName, FilmReleaseDate FROM tblFilm

OPEN FilmCursor

FETCH NEXT FROM FilmCursor

Take care when doing this as some settings are mutually exclusive. For instance, you can't declare a FAST_FORWARD cursor which also has the SCROLL option specified:

DECLARE FilmCursor CURSOR SCROLL FAST_FORWARD

FOR SELECT FilmID, FilmName, FilmReleaseDate FROM tblFilm

OPEN FilmCursor

FETCH NEXT FROM FilmCursor

Attempting to execute the code above results in the following error message:

Error message

If only all errors were described this clearly!

 

What's Next?

For the final part of this blog series we'll look at how you can use a cursor to update the data in a record set.

This blog has 0 threads Add post