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. 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.
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.
In a previous Tech Republic column about SQL Server 2005, I showed how you can convert some of your iterative queries (cursors) to set-based operations in order to enhance performance.
You should take this approach in most circumstances, as you'll potentially see performance gains when switching from a looping construct to a set-based construct.
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.
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.
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.