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.
