Fetching Records with an SQL Cursor
When you're using a cursor in SQL you use the FETCH statement to move the cursor to a particular record. This part of the series examines the FETCH statement in more detail.
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.
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. |
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:
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.
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:
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.
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:
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.
What's Next?
The next part of this blog series investigates the different types of cursor that you can use in SQL.
