Cursors in SQL

Most of the actions you perform in SQL are applied to an entire set of records at the same time; these are often referred to as set-based operations.  Set-based operations are what make SQL so efficient when it comes to processing large sets of data and, in most cases, you can achieve the results you need using only this type of operation. Occasionally, however, you may find that you need to process just a single record at a time and, if you want to do this, you'll need to learn how to use cursors.

Cursors move through a set of records one row at a time.

A Note on Cursor Performance

Before we talk about how to use cursors it's worth mentioning something about their performance: cursors are s-l-o-w! Processing one record at a time will never be as fast as an equivalent set-based operation; if speed is important then cursors are not the tools you should be using.

So Why Use Cursors?

If cursors are so slow compared to set-based operations why would you use them? The simple answer is that there are certain things that you simply can't do with a set-based operation, for instance executing a stored procedure against each individual record in a table.

Even when it is possible to achieve the result you want with a set-based operation you might even find that you want to use a cursor simply because it's easier to understand the code. Sometimes clarity and ease-of-use is more important than performance.

Bearing all of this in mind the next part of this blog shows you how to declare and use a simple cursor to process a list of films.

In order to use a cursor you must first declare it. When you do this you can specify, amongst other things, the type of cursor you are going to use and which set of records it is going to process.

The Syntax of a Cursor Declaration

To use the most basic, default cursor you will need to declare it, give it a name and say which set of records it will process.  The two lines of code necessary to do this are shown below:

You can use any standard SELECT statement when you specify the set of records that you want your cursor to process.

Opening and Closing Cursors

Once you've declared a cursor, in order to use it you need to open it.  The code to do this is fairly straightforward!

Opening the cursor also executes the SELECT statement and loads the set of records specified ready for the cursor to begin processing.

When you've finished with whatever task you're using the cursor for you should close it and remove any references to it.  The two lines required to do this are shown below:

The full set of code required to declare, open and close a simple cursor is shown below:

Making a Cursor Step Through a Set of Records

Of course, simply opening and then closing a cursor doesn't perform any sort of useful task; it's what you do in between that determines what the cursor does. As a minimum you would want the cursor to move through the set of records until it reaches the end.  In order to do this you would first need to move the cursor to the first record:

After this you need to tell the cursor to keep moving until it reaches the end of the set of records. You can achieve this using a WHILE loop which checks the value of a global variable called @@FETCH_STATUS. The code to do this is show below:

The full procedure to use a cursor to step through a set of records is shown below:

The results of executing this code is shown in the diagram below:

Each record is returned as a separate result and takes significantly longer than the equivalent set-based operation.

Checking the Fetch Status

In the example above we tested whether the value of the @@FETCH_STATUS variable was 0 in order to determine whether the cursor should continue to process the set of records.

The @@FETCH_STATUS variable has three possible values, as shown in the table below:

Specifying Which Record to Fetch

In our basic example we used the FETCH NEXT statement to move the cursor to whichever record was the next one in the result set. There are several other ways to use the FETCH statement, as shown in the table below:

So, as a pointless example we could step over our set of films in reverse order using the code shown below:

Fetching Records into Variables

In our example so far the result of executing the code is a single output for each record that the cursor passes over:

Each record gets its own separate output.

Rather than outputting a result we can use our cursor to read the values of a record into variables using the INTO statement:

Executing the code now will simply tell us that it completed successfully, but we don't see any actual results:

This is the only output you'll see when executing the above code.

In order to see some results we need to do something with the values we've captured in our variables. As an example we could replace the WHILE loop in the above example with the one shown below:

Before executing the code we can switch the output of the query to text, either by clicking the tool shown below or by pressing CTRL + T on the keyboard:

Click this tool to change the output to the text window.

When we execute the code now we can see the output in the text window, rather than in the grid.

The output of the code looks like this.

Executing Stored Procedures with a Cursor

Perhaps the most useful thing that you can do with a cursor is use it to execute a stored procedure against each record in a result set.  To replicate the above example using a stored procedure we would first need to create one which can accept a number of parameters. Code to do this is shown below:

All that we would need to do then is to call this stored procedure within our WHILE loop, like so:

The complete routine to call the stored procedure using the cursor would look like this:

Of course, in the real world your stored procedure would do something more elaborate than simply list some records, but the principles would remain the same as in our simple example.

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:

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:

The error message you would see is shown below:

The cursor is out of scope.

With a global cursor the code will work:

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.

2. In the dialog box which appears, select the Options category from the list on the left hand side.

Choose to view the Options page.

3. Set the value of the Default Cursor option and click OK.

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:

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:

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

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:

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:

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:

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:

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:

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

If only all errors were described this clearly!

Declaring a Cursor for Updating Records

In this final part we'll look at how you can use a cursor to modify data as well. The easiest way to declare a cursor that can be used to update records is to simply use the default cursor type:

Using the code above will declare a cursor which can used to update records. You can make things a little more obvious by explicitly stating that the cursor is to be used for updating, as shown below:

This cursor can be used to update any field from the underlying data set. To make things even more specific you can list which columns from the data set you would like to make updatable, as shown below:

The code above ensures that only one field from the underlying data set can be updated. You can add more column names in a comma-separated list if you need to make more fields updatable.

Creating a Running Total with a Cursor

To demonstrate how to use a cursor to update records we'll create a running total of the number of Oscars our films have won. To begin with we'll declare a couple of variables and set their default values.

The first variable will hold the running total of Oscars while the second will be used to read each individual film's Oscars as the cursor moves through the record set.

Next, we'll declare our cursor, open it and move it to the first record. At the same time we'll read the first film's Oscar wins into the variable we declared earlier.

Now we can begin looping through the record set. We'll start by setting up the basic structure of the loop and adding a quick test to ensure that our variable is accumulating the number of Oscars.

The result of executing the code should be a running total of the Oscars in the Messages window:

The final step is to transfer these results into the table.

To put the results we've calculated into the table we can replace our simple PRINT statement with an UPDATE statement. To make sure the correct values are added to the correct records we use the WHERE CURRENT OF statement to check the current position of our cursor.  The full set of code required to make this system work is shown below:

To see the results of this procedure we'll need to select some data from the underlying table.

The results of this query are shown in the diagram below:

A fully populated column of data.