Using Cursors in SQL Server
Part five 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
  5. Updating Records Using a Cursor (this blog)

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.

Updating Records Using a Cursor

So far in this series we've been concerned with simply reading data from tables using a cursor. In this final part we'll look at how you can use a cursor to modify data as well.

Declaring a Cursor for Updating Records

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.

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.

--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:

Messages

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:

Result of cumulative Oscars

A fully populated column of data.

 
This blog has 0 threads Add post