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 Access
SQL Scripts & Samples
» Database Forum
» Slideshows
» Sitemap
Free Newsletters:

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


Posted Nov 5, 2004

Cursors with SQL 2000 Part 2

By Don Schlichting


This is the second article in the Cursors with SQL 2000 series. In the preceding article, cursor use and basic syntax were covered. A select cursor was created demonstrating the keywords DECLARE, OPEN, FETCH, @@FETCH_STATUS, and DEALLOCATE. These keywords guide basic cursor execution by assigning a TSQL statement to the cursor, moving records into the cursor, retrieving a specific record from the cursor, working with retrieved record, and then closing the cursor. The sample cursor demonstrated that SQL could act on one record at a time, as opposed to its usual method of acting on sets of data. In this article, the sample cursor will be expanded upon to include data modification and record positioning.

Following is the sample cursor from the first article. This example demonstrates the minimum statements required to create and use a cursor.

USE pubs

	SELECT price FROM titles

OPEN get_price


	FETCH NEXT FROM get_price

CLOSE get_price
DEALLOCATE get_price


The purpose of the this cursor was to lay the ground work for a statement that will change books priced under $20 to be raised by 10%, while books currently $20 or more, will be raised 5%. The above cursor will now be modified so prices returned from titles under twenty dollars will print "Under 20", while titles twenty dollars or more will print the price. Modify the cursors to:

DECLARE @price money

SET @get_price = CURSOR FOR
     SELECT price FROM titles

OPEN @get_price
FETCH NEXT FROM @get_price INTO @price

     IF @Price < 20 
	SELECT 'Under 20'
	SELECT @Price

     FETCH NEXT FROM @get_price INTO @price

CLOSE @get_price
DEALLOCATE @get_price

One of the first differences to notice is the use of variables for the cursor and the price. Now that the cursor is a variable, it is SET to a SQL statement. The SQL statement itself has not changed. The cursor is opened like before, but the FETCH will move the field price into the variable @price. If more than one column were needed, say price and title, we would declare two variables, then SELECT INTO both as follows:

DECLARE @Price money, @Title varchar(50), @get_price CURSOR

SET @get_price = CURSOR FOR
     SELECT Price, Title FROM titles

OPEN @get_price

FETCH NEXT FROM @get_price INTO @Price, @Title

The WHILE loop is still controlled with @@FETCH_STATUS, the use of variables does not alter this. Once fetched, @price can be used like a normal variable as demonstrated by "IF @Price < 20". Because the WHILE loop has more than one action line, it uses BEGIN and END tags. Once we are done working with this record, the next is fetched. At end of file, the loop exits and the cursor is closed and destroyed.

Running the cursor will produce the expected list of "Under," price, and nulls, depending on the value found in the titles table.

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