Processing Sequentially Through a Set of Records

November 19, 2003
T-SQL Programming Part 3 - Processing Sequentially Through a Set of Records
By Gregory A. Larsen

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 intdeclare CustList cursor forselect top 5 CustomerID from Northwind.dbo.CustomersOPEN CustListFETCH NEXT FROM CustList INTO @CustIdset @RowNum = 0 WHILE @@FETCH_STATUS = 0BEGIN set @RowNum = @RowNum + 1 print cast(@RowNum as char(1)) + ' ' + @CustId FETCH NEXT FROM CustList INTO @CustIdENDCLOSE CustListDEALLOCATE CustList

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


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 intselect top 1 @CustId=CustomerID from Northwind.dbo.Customersset @RowNum = 0 WHILE @RowNum < 5BEGIN set @RowNum = @RowNum + 1 print cast(@RowNum as char(1)) + ' ' + @CustId select top 1 @CustId=CustomerID from Northwind.dbo.Customers where CustomerId > @CustIDEND

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


Cloud Intelligence Leading the Digital Future

Alibaba Cloud ACtivate Online Conference, Nov. 20th & 21st, 2019 (UTC+08)

Register Now >

Starter Package

SSD Cloud server and data transfer for only $2.50 a month

Get Started >

Alibaba Cloud Free Trial

Learn and experience the power of Alibaba Cloud with a free trial worth $300-1200 USD

Learn more >



如果您發現本社區中有涉嫌抄襲的內容,歡迎發送郵件至: 進行舉報並提供相關證據,工作人員會在 5 個工作天內聯絡您,一經查實,本站將立刻刪除涉嫌侵權內容。