T-SQL Programming Part 3 – Processing Sequentially Through a Set of Records

At some point you will have
some business logic that will require you to process sequentially through a set
of records one record at a time. For example you may have a list of databases,
and for each database you may want to build a command that will perform some
process against each database. Or you might have a set of records where you
want to process through each record one at a time, so you can select additional
information from another table based on the information contained in each
record. This article will discuss two different ways to process through a set
of records one record at a time.

Using a Cursor

The first method I will
discuss uses a cursor to process through a set of records one record at a
time. A cursor is basically a set of rows that you define based on a record
set returned from a query. A cursor allows applications a mechanism to process
through a result set one row at a time. With a cursor an application is
allowed to position itself to a specific row, scroll back and forth, and a
number of other things. It would take a series of articles to describe all the
functionality of a cursor. For the purpose of this article I’m only going to focus
on how to use the default scrolling functionality of a cursor. This default
functionality will only read from the first row to the last row in a cursor,
one row at a time. I will leave additional cursor topics to another article
series.

To define a cursor the
DECLARE CURSOR statement is used. Here is the basic format for the simple
cursor topic I will be discussing in this article.

DECLARE cursor_name CURSOR 
	FOR select_statement

The cursor_name is
the name you want to associate with the cursor. The select_statement is
the query that will determine the rows that make up the cursor. Note there are
other parameters/options associated with the DECLARE CURSOR statement that help
define more complicated cursor processing than I will be covering in this
article. For these additional options please read Microsoft SQL Server Books
Online.

Let’s review a fairly simple
cursor example. This example will define a cursor that contains the top 5 Customer_Id’s
in the Customer table in the Northwind database. It will then process through
each record displaying a row number and the CustomerID for each. Here is the
code to do this.


declare @CustId nchar(5)
declare @RowNum int
declare CustList cursor for
select top 5 CustomerID from Northwind.dbo.Customers
OPEN CustList
FETCH NEXT FROM CustList
INTO @CustId
set @RowNum = 0
WHILE @@FETCH_STATUS = 0
BEGIN
set @RowNum = @RowNum + 1
print cast(@RowNum as char(1)) + ‘ ‘ + @CustId
FETCH NEXT FROM CustList
INTO @CustId
END
CLOSE CustList
DEALLOCATE CustList

Here are the results that
are generated from the print statement when I run it against my Northwind
Database.


1 ALFKI
2 ANATR
3 ANTON
4 AROUT
5 BERGS

Let’s look at the above code
in a little more detail. I first declared a cursor called “CustList”. The “CustList”
cursor is populated using a SELECT statement that uses the TOP clause to return
only the top 5 CustomerId’s. Next the cursor is opened. Each record in the “CustList”
cursor is retrieved, one record at a time, using the “FETCH NEXT” next
statement. The “FETCH NEXT” statement populates the local variable @CustID with
the CustomerID of the current record being fetched. The @@FETCH_STATUS
variable controls whether the WHILE loop is executed. @@FETCH_STATUS is set to
zero when a record is successfully retrieved from the cursor “CustList”.
Inside the WHILE loop the @RowNum variable is incremented by 1 for each record
processed. The calculated Row Number and @CustId are then printed out. Lastly,
a “FETCH NEXT” statement is used to retrieve the next row before the next cycle
of the WHILE loop. This process continues one record at a time until all
records in cursor “CustList” have been processed.

Using a Select Statement

You can also use a SELECT
statement to process through a set of records one record at a time. To do this
I will issue an initial SELECT statement that will return the first row, then a
series of follow on SELECT statements where each SELECT statement retrieves the
next row. This is done by using the “TOP 1” clause of the SELECT statement,
and a WHERE statement.

I will use the same example
as above and only return the top 5 CustomerID’s from the Northwind database
Customers table. In this code I will use two different “SELECT TOP 1” statements
and a WHILE loop to return all 5 records. Each record will be processed one at
a time.


declare @CustId nchar(5)
declare @RowNum int
select top 1 @CustId=CustomerID from Northwind.dbo.Customers
set @RowNum = 0
WHILE @RowNum < 5
BEGIN
set @RowNum = @RowNum + 1
print cast(@RowNum as char(1)) + ‘ ‘ + @CustId
select top 1 @CustId=CustomerID from Northwind.dbo.Customers
where CustomerId > @CustID
END

Here you can see the first SELECT
statement selects only the first CustomerID. This ID is placed in the local
variable @CustID. The WHILE loop is controled by the local variable @RowNum.
Each time through the WHILE loop, the Row Number and CustomerID are printed out.
Prior to returning to the top of the WHILE loop I used another “SELECT TOP 1”
statement to select the next CustomerID. This SELECT statement uses a WHERE
clause on the SELECT statement to select the first CustomerID that is greater
than the CustomerID that was just printed. The WHILE loop is process 5 times,
allowing the SELECT TOP 1 method to retrieve the top 5 CustomerID’s one records
at a time. This example produces the same printed output as my prior CURSOR
example.

Conclusion

Hopefully this article has
given you some ideas on how to use a CURSOR, and a SELECT statement to process
through a set of records. I use both of these methods, although I find using a
SELECT statement to be a little simpler to code. You will need to decide which
solution makes the most sense in your environment.

»


See All Articles by Columnist
Gregory A. Larsen

Gregory Larsen
Gregory Larsen
Gregory A. Larsen is a DBA at Washington State Department of Health (DOH). Greg is responsible for maintaining SQL Server and other database management software. Greg works with customers and developers to design and implement database changes, and solve database/application related problems. Greg builds homegrown solutions to simplify and streamline common database management tasks, such as capacity management.

Latest Articles