Using Cursors in SQL Server
Part three 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 (this blog)
  4. SQL Cursor Options
  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.

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:

Output of cursor

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:

Results of using variables

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:

Changing output to text

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.

Output from printing

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.

This blog has 0 threads Add post