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'll have some business logic that'll require your to process sequentially through a set of records one Record in a time. For example your may have a list of databases, and for each database you could want to build a command that would perform some Process against each database. Or you might have a set of the records where you want the to process through each record one in a time, so can select addition Al information from another table based to the information contained in each record. This article would discuss two different ways to process through a set of records one record in a time.
Using a Cursor
The "I" I would discuss uses a cursor to process through a set of records one record in a time. A cursor is basically a set of rows this you define based in a record set returned from a query. A cursor allows applications a mechanism to process through a, set one row at a time. With a cursor a application are allowed to position itself to a specific row, scroll back and forth, and a 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" and "" "to" use "the default scrolling functionality of a cursor. This default functionality would only read from the "in" to "last row" a cursor, one row at a time. I'll leave additional cursor topics to another article.

To define a cursor the DECLARE cursor statement is used. The ' basic ' format for the ' Simple cursor topic I'll be discussing into this article.

DECLARE cursor_name Cursor for select_statement

The cursor_name is the name of your want to associate with the cursor. The select_statement is the query, that would determine the rows, the cursor. Note There are, parameters/options associated with the DECLARE CURSOR statement This help define more complicated SOR processing than I'll be covering in this article. For this additional options please read Microsoft SQL Server books Online.

Let ' s review a fairly simple cursor example. This example would define a cursor that contains the top 5 customer_id's in the Customer table in the Northwind database. It would then process through each record displaying a row number and the CustomerID for each. Here are the code to does this.

DECLARE @CustId NCHAR (5) DECLARE @RowNum intdeclare custlist cursor forselect Top 5 CustomerID from Northwind.dbo.Customers OPEN Custlistfetch NEXT from custlist to @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" are generated from the "print statement when I run" it against my Northwind Database.

1 ALFKI2 ANATR3 ANTON4 AROUT5 Bergs

Let's look at the above code in a little more detail. I declared a cursor called "custlist". The "custlist" cursor is populated using a SELECT statement which uses the TOP clause to return only the top 5 CustomerId ' s . Next the cursor is opened. Each of the record in the "custlist" cursor are retrieved, one record at a time, using the "FETCH next" Next statement. The "FETCH NEXT" statement populates the local variable @CustID and 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 is successfully retrieved from the cursor "custlist". Inside the while loop the @RowNum variable are 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 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 in a time. To does this I'll issue an initial SELECT statement that'll return the "a", then a series of follow on select Stat Ements where each SELECT statement retrieves the next row. This is doing by using the ' top 1 ' clause the SELECT statement, and a WHERE statement.

I'll use the same example as above to return to the top 5 CustomerID's from the Northwind database Customers table. In this code I would use two different ' select top 1 ' statements and a while loop to return all 5 records. Each of the record would be processed one in a time.

DECLARE @CustId NCHAR (5) Declare @RowNum intselect top 1 @CustId =customerid from Northwind.dbo.Customersset @RowNum = 0 WHI LE @RowNum < 5BEGIN set @RowNum = @RowNum + 1 Print cast (@RowNum as char (1)) + ' + @CustId select top 1 @CustId =custo Merid from Northwind.dbo.Customers where CustomerId > @CustIDEND

Here you can have a statement selects only the "the" the "the" " This ID was placed in the local variable @CustID. The while loop are 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 ' while ' Loop I used another ' select top 1 ' statement to select the next CustomerID. This SELECT statement uses a WHERE clause in the SELECT statement to select the the "the" the "is CustomerID" the CustomerID that is just printed. The while loop are process 5 times, allowing the "SELECT top 1" to retrieve "Top 5 CustomerID '" One Records at a ti Me. This example produces the same printed output as my prior CURSOR.
Conclusion
Hopefully this article has given for you some ideas on "How to" a CURSOR, and a SELECT statement to process through a set of Records. I use both of the methods, although I find using a SELECT statement to is a little simpler to code. You'll need to decide which solution makes the most sense in your.