Step 3: Fetch and Process

Here is where all the work occurs. The following code fetches a row into local variables. You may also fetch the columns directly, which will print them to the screen just as a normal SELECT would. In this example, I use the PRINT statement to send results to the screen. Because I’m using PRINT, I don’t have column headers provided automatically for me. Many cursor programs are used for server processing only, and do not return rows in this fashion. As you can see, it’s a lot less convenient than a simple SELECT.

set nocount on
declare @type char(12), @price money, @title varchar(80),
         @tempstr varchar(40)
print “Title                    Price”
print “----------------------   ----------”
fetch next from titles_curs into @title, @type, @price
while @@fetch_status = 0 begin
    if @type = ‘business’
       select @price = @price * 1.25
    if @price is null
    select @tempstr = convert(char(22), @title) + “ --No pricing--”
    else
      select @tempstr = convert(char(22), @title) + convert(char(7),
@price)
   print @tempstr
    fetch next from titles_curs into @title, @type, @price
end

Title                         Price
------------------            ----------
The Psychology of Comp        --No pricing--
Cooking  with  Computers      14.94
Straight  Talk  About  Co     24.99
The  Busy  Executive’s D      24.99
You  Can  Combat  Compute      3.74
Silicon  Valley  Gastron      19.99
The  Gourmet  Microwave        2.99
But  Is  It  User  Friendl    22.95
Net  Etiquette                --No  pricing--
Secrets  of  Silicon  Val     20.00
Computer  Phobic  AND  No     21.59
Emotional  Security:  A        7.99
Is  Anger  the  Enemy?        10.95
Life  Without  Fear            7.00
Prolonged  Data  Depriva      19.99
Fifty  Years  in  Bucking     11.95
Onions,  Leeks,  and  Gar     20.95
Sushi,  Anyone?               14.99

I set nocount on to eliminate the n rows affected messages. Otherwise, every time a fetch was performed, I would have gotten a message. This would have made the output rather unappealing.

At the beginning of the procedure, I declared some variables to hold fetched values. These variables must be of the same type as the columns that are being fetched. There is no implicit conversion during fetch, so if you try to fetch a money column into an integer data type, you will receive a conversion error.

After declaring local work variables, I print the column headers. This is just a couple of simple PRINT statements. If you were processing this inside an application program, the prints wouldn’t be returned as normal results. Instead, they would go through the application’s message handler.

The first fetch occurs on line six. This gets the first row in the cursor keyset.

A cursor keyset refers to the set of rows that are part of this cursor’s scope. For sensitive cursors (also called dynamic cursors), if a row is deleted from the keyset, the row remains in the set, but will return null values for all columns if fetched.

It also seeds the @@fetch_status variable. The @@fetch_status variable will always have one of three values. If the most recent fetch succeeded, the @@fetch_status will be 0. If the most recent fetch failed because there are no more rows to fetch (or in the case of moving too far ahead or too far backward, the fetch landed outside the current keyset), @@fetch_status will be –1. If the row no longer exists (you are using a sensitive cursor and the underlying row was deleted), the @@fetch_status will be –2, and all row values will be null.

The loop that I have set up will fetch rows until a –1 is returned. If there is a problem where a row gets deleted in the middle of the set (and @@fetch_status is –2), that will abort the loop, too.

Inside the loop, the value of @type is tested. If the current row is a business book, I increase the price by 25 percent. Then, the value of @price is tested. If the price is null, print a custom message rather than null. Finally, the messages are all gathered together in a temporary work string and printed to the console. The next row is fetched, and the loop begins again.

Close the Cursor

After the cursor has fetched itself out, close the cursor.

close titles_curs

Closing the cursor destroys the cursor keys et. Put another way, closing the cursor wipes out the result set on which the cursor is operating. The cursor declaration is still active, however, and may bereopened with an OPEN statement after it has been closed. You may want to do this if you are using an insensitive cursor and want to refresh the result set.

Deallocating the Cursor

Deallocation frees the memory on the server that has been set aside for the cursor. Deallocation is the final “cleanup” step.

deallocate titles_curs

If you do not deallocate the cursor, memory will remain tied up until your connection is closed. If you have declared a cursor, you may not declare another with the same name until you have deallocated the old one.