dcsimg
Database Journal
MS SQL Oracle DB2 Access MySQL PostgreSQL Sybase PHP SQL Etc SQL Scripts & Samples Tips Database Forum

» Database Journal Home
» Database Articles
» Database Tutorials
MS SQL
Oracle
DB2
MS Access
MySQL
» RESOURCES
SQL Scripts & Samples
Tips
» Database Forum
» Slideshows
» Sitemap
Free Newsletters:
DatabaseDaily  

By submitting your information, you agree that databasejournal.com may send you databasejournal offers via email, phone and text message, as well as email offers about other products and services that databasejournal believes may be of interest to you. databasejournal will process your information in accordance with the Quinstreet Privacy Policy.

News Via RSS Feed


Database Journal |DBA Support |SQLCourse |SQLCourse2
 

Featured Database Articles

MS SQL

Posted Nov 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 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



MS SQL Archives

Comment and Contribute

 


(Maximum characters: 1200). You have characters left.

 

 




Latest Forum Threads
MS SQL Forum
Topic By Replies Updated
SQL 2005: SSIS: Error using SQL Server credentials poverty 3 August 17th, 07:43 AM
Need help changing table contents nkawtg 1 August 17th, 03:02 AM
SQL Server Memory confifuration bhosalenarayan 2 August 14th, 05:33 AM
SQL Server Primary Key and a Unique Key katty.jonh 2 July 25th, 10:36 AM