Read our blogs, tips and tutorials
Try our exercises or test your skills
Watch our tutorial videos or shorts
Take a self-paced course
Read our recent newsletters
License our courseware
Book expert consultancy
Buy our publications
Get help in using our site
548 attributed reviews in the last 3 years
Refreshingly small course sizes
Outstandingly good courseware
Whizzy online classrooms
Wise Owl trainers only (no freelancers)
Almost no cancellations
We have genuine integrity
We invoice after training
Review 30+ years of Wise Owl
View our top 100 clients
Search our website
We also send out useful tips in a monthly email newsletter ...
Written by Andrew Gould
In this tutorial
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.
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.
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.
We have removed cursors from our advanced SQL course, for good reason - we never use them ourselves. Before becoming a cursors guru, you should ask yourself if you should be doing what you're trying to do in a different way.
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.
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:
--Declare a cursor and give it a name
DECLARE FilmCursor CURSOR
--Now say which set of records the cursor will process
FOR SELECT FilmID, FilmName, FilmReleaseDate FROM tblFilm
You can use any standard SELECT statement when you specify the set of records that you want your cursor to process.
Once you've declared a cursor, in order to use it you need to open it. The code to do this is fairly straightforward!
--Open the cursor and load the set of records specified
OPEN FilmCursor
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:
--Tidy up by first closing the cursor,
CLOSE FilmCursor
--then removing any references to it
DEALLOCATE FilmCursor
The full set of code required to declare, open and close a simple cursor is shown below:
DECLARE FilmCursor CURSOR
FOR SELECT FilmID, FilmName, FilmReleaseDate FROM tblFilm
OPEN FilmCursor
--DO SOMETHING USEFUL HERE
CLOSE FilmCursor
DEALLOCATE FilmCursor
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:
--Move the cursor to the first record
FETCH NEXT FROM FilmCursor
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:
--Continue moving to the next record until there are no more
WHILE @@FETCH_STATUS = 0
FETCH NEXT FROM FilmCursor
The full procedure to use a cursor to step through a set of records is shown below:
DECLARE FilmCursor CURSOR
FOR SELECT FilmID, FilmName, FilmReleaseDate FROM tblFilm
OPEN FilmCursor
FETCH NEXT FROM FilmCursor
WHILE @@FETCH_STATUS = 0
FETCH NEXT FROM FilmCursor
CLOSE FilmCursor
DEALLOCATE FilmCursor
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.
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.
DECLARE FilmCursor CURSOR
FOR SELECT FilmID, FilmName, FilmReleaseDate FROM tblFilm
OPEN FilmCursor
FETCH NEXT FROM FilmCursor
WHILE @@FETCH_STATUS = 0
FETCH NEXT FROM FilmCursor
CLOSE FilmCursor
DEALLOCATE FilmCursor
The @@FETCH_STATUS variable has three possible values, as shown in the table below:
Value of Fetch Status | Description |
---|---|
0 | The fetch was successful, i.e. the cursor is pointing at an existing record. |
-1 | The fetch failed or the row you're trying to fetch is outside the scope of the result set. |
-2 | The fetched row is missing. |
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:
Statement | Description | Example |
---|---|---|
FETCH NEXT | Moves the cursor to the next record in the result set. If this is the first time a fetch has been used on this cursor it is moved to the first record. | FETCH NEXT FROM FilmCursor |
FETCH PRIOR | Moves the cursor to the previous row in the result set. | FETCH PRIOR FROM FilmCursor |
FETCH FIRST | Moves the cursor to the first record in the result set. | FETCH FIRST FROM FilmCursor |
FETCH LAST | Moves the cursor to the last record in the result set. | FETCH LAST FROM FilmCursor |
FETCH ABSOLUTE n | Moves the cursor to the specified record in the result set where n is the number of the row you want to return. If you specify a negative number for n it will return the record that is n rows before the end of the result set. | FETCH ABSOLUTE 5 FROM FilmCursor |
FETCH RELATIVE n | Moves the cursor the specified number of rows forwards or backwards from its current position. Use positive numbers to move forwards and negative numbers to move backwards | FETCH RELATIVE -3 FROM FilmCursor |
So, as a pointless example we could step over our set of films in reverse order using the code shown below:
DECLARE FilmCursor CURSOR SCROLL
FOR SELECT FilmID, FilmName, FilmReleaseDate FROM tblFilm
OPEN FilmCursor
FETCH LAST FROM FilmCursor
WHILE @@FETCH_STATUS = 0
FETCH PRIOR FROM FilmCursor
CLOSE FilmCursor
DEALLOCATE FilmCursor
Note that in order to be allowed to move backwards through the result set we've specified the type of cursor as SCROLL. The next part of this series will discuss cursor types in more detail.
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:
DECLARE @ID AS INT
DECLARE @Title AS VARCHAR(MAX)
DECLARE @Release AS DATETIME
DECLARE FilmCursor CURSOR
FOR SELECT FilmID, FilmName, FilmReleaseDate FROM tblFilm
OPEN FilmCursor
FETCH NEXT FROM FilmCursor
INTO @ID, @Title, @Release
WHILE @@FETCH_STATUS = 0
FETCH NEXT FROM FilmCursor
INTO @ID, @Title, @Release
CLOSE FilmCursor
DEALLOCATE FilmCursor
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:
WHILE @@FETCH_STATUS = 0
BEGIN
PRINT @Title + ' released on ' + CONVERT(CHAR(10),@Release,103)
PRINT 'List of Characters'
PRINT '=================='
SELECT
CastCharacterName
FROM
tblCast
WHERE
CastFilmID = @ID
FETCH NEXT FROM FilmCursor
INTO @ID, @Title, @Release
END
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.
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:
CREATE PROC spListCastMembers
(
@FilmID INT
,@FilmTitle VARCHAR(MAX)
,@FilmRelease DATETIME
)
AS
BEGIN
PRINT @FilmTitle + ' released on ' + CONVERT(CHAR(10),@FilmRelease,103)
PRINT 'List of Characters'
PRINT '=================='
SELECT
CastCharacterName
FROM
tblCast
WHERE
CastFilmID = @FilmID
END
All that we would need to do then is to call this stored procedure within our WHILE loop, like so:
WHILE @@FETCH_STATUS = 0
BEGIN
EXEC spListCastMembers @ID, @Title, @Release
FETCH NEXT FROM FilmCursor
INTO @ID, @Title, @Release
END
The complete routine to call the stored procedure using the cursor would look like this:
DECLARE @ID AS INT
DECLARE @Title AS VARCHAR(MAX)
DECLARE @Release AS DATETIME
DECLARE FilmCursor CURSOR
FOR SELECT FilmID, FilmName, FilmReleaseDate FROM tblFilm
OPEN FilmCursor
FETCH NEXT FROM FilmCursor
INTO @ID, @Title, @Release
WHILE @@FETCH_STATUS = 0
BEGIN
EXEC spListCastMembers @ID, @Title, @Release
FETCH NEXT FROM FilmCursor
INTO @ID, @Title, @Release
END
CLOSE FilmCursor
DEALLOCATE FilmCursor
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.
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:
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:
In the Object Explorer, right-click on the name of your database and choose Properties.
In the dialog box which appears, select the Options category from the list on the left hand side.
Choose to view the Options page.
Set the value of the Default Cursor option and click OK.
Choose from the two options in the drop down list.
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:
You need to use a SCROLL cursor if you want to use FETCH FIRST.
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 |
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. |
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:
If only all errors were described this clearly!
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:
DECLARE FilmCursor CURSOR
FOR SELECT FilmOscarWins FROM tblFilm
OPEN FilmCursor
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:
DECLARE FilmCursor CURSOR
FOR SELECT FilmOscarWins FROM tblFilm
FOR UPDATE
OPEN FilmCursor
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:
DECLARE FilmCursor CURSOR
FOR SELECT FilmOscarWins FROM tblFilm
FOR UPDATE OF FilmCumulativeOscars
OPEN FilmCursor
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.
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.
--Variable for the running total of Oscars
DECLARE @TotalOscars INT
--Variable for each film's Oscars
DECLARE @FilmOscars INT
SET @TotalOscars = 0
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.
DECLARE FilmCursor CURSOR
FOR SELECT FilmOscarWins FROM tblFilm
FOR UPDATE OF FilmCumulativeOscars
OPEN FilmCursor
FETCH NEXT FROM FilmCursor INTO @FilmOscars
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.
WHILE @@FETCH_STATUS = 0
BEGIN
SET @TotalOscars += @FilmOscars
--This is where we'll update the records
--As a test we'll print the value of our variable
PRINT @TotalOscars
FETCH NEXT FROM FilmCursor INTO @FilmOscars
END
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:
DECLARE @TotalOscars INT
DECLARE @FilmOscars INT
SET @TotalOscars = 0
DECLARE FilmCursor CURSOR
FOR SELECT FilmOscarWins FROM tblFilm
FOR UPDATE OF FilmCumulativeOscars
OPEN FilmCursor
FETCH NEXT FROM FilmCursor INTO @FilmOscars
WHILE @@FETCH_STATUS = 0
BEGIN
SET @TotalOscars += @FilmOscars
UPDATE tblFilm
SET FilmCumulativeOscars = @TotalOscars
WHERE CURRENT OF FilmCursor
FETCH NEXT FROM FilmCursor INTO @FilmOscars
END
CLOSE FilmCursor
DEALLOCATE FilmCursor
To see the results of this procedure we'll need to select some data from the underlying table.
SELECT
FilmName
,FilmOscarWins
,FilmCumulativeOscars
FROM
tblFilm
The results of this query are shown in the diagram below:
A fully populated column of data.
Kingsmoor House
Railway Street
GLOSSOP
SK13 2AA
Landmark Offices
99 Bishopsgate
LONDON
EC2M 3XD
Holiday Inn
25 Aytoun Street
MANCHESTER
M1 3AE
© Wise Owl Business Solutions Ltd 2024. All Rights Reserved.