Cursors with SQL 2000 Part 2

Introduction

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
GO

DECLARE get_price CURSOR FOR
SELECT price FROM titles

OPEN get_price

FETCH NEXT FROM get_price

WHILE @@FETCH_STATUS = 0
FETCH NEXT FROM get_price

CLOSE get_price
DEALLOCATE get_price

SELECT INTO

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
DECLARE @get_price CURSOR

SET @get_price = CURSOR FOR
SELECT price FROM titles

OPEN @get_price

FETCH NEXT FROM @get_price INTO @price

WHILE (@@FETCH_STATUS = 0)
BEGIN
IF @Price < 20
SELECT ‘Under 20’
ELSE
SELECT @Price

FETCH NEXT FROM @get_price INTO @price
END

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.

Don Schlichting
Don Schlichting
Don Schlichting is a Microsoft MSCE, MCDBA and an Oracle Certified Associate. Working at Professional Control Corporation as the IT Director,.

Latest Articles