Working with Cursors
In order to understand how to use a cursor, we will build a T-SQL script that uses a cursor, one step at a time, explaining the process as we go. The example will read rows from the Products table. If the product is from Germany, it will add 25% to the unit cost of the product. It will also produce a report of all unit costs and any changes that were made.
Note
The final code for this example can be found at the end of this section and on the CD-ROM.
Declaring the Cursor
The first step you must perform is to declare or create the cursor, associating it with a SELECT statement. The syntax of the DECLARE statement used to create a cursor is
Declare <cursor name> [insensitive] [scroll] cursor For <select statement> [for <read only | update [of <column list>]>]
This syntax is the ANSI-92 standard for declaring a cursor. Microsoft SQL Server 2000 provides a T-SQL extended syntax for the declare cursor statement, which is shown in the following code. Table 14.2 lists each of the different keywords and their descriptions.
Declare <cursor_name> cursor [ local | global ] [ forward only | scroll ] [ static | keyset | dynamic | fast_forward ] [ read_only | scroll_locks | optimistic ] [ type_warning ] For <select_statement> [ for update [ of <column list>] ]
You cannot mix the two forms of the DECLARE CURSOR statement. If you specify the SCROLL or INSENSITIVE keywords before the CURSOR keyword, you cannot use any keywords between the CURSOR and the FOR <select_statement> keywords. If you specify any keywords between the CURSOR and the FOR <select_statement> keywords, you cannot specify SCROLL or INSENSITIVE before the CURSOR keyword.
If you use T-SQL syntax when declaring a cursor and do not specify READ_ONLY, OPTIMISTIC, or SCROLL_LOCKS, the default actions are as follows:
If the SELECT statement does not support updates (insufficient permissions, accessing remote tables that do not support updates, and so on), the cursor is READ_ONLY.
You can select all the columns or specify some of the columns for the cursor, but allow updates to only some of those columns by providing an update column list, as shown in line 3 of the following syntax.
Declare <cursor name> [insensitive] [scroll] cursor For <select statement> [for <read only | update [of <column list>]>]
To declare the cursor for our example, the following SQL code is used:
use northwind
declare crs_products cursor
for
select p.productid, p.productname, p.unitprice,
s.country, s.supplierid, s.companyname
from products as p inner join
suppliers as s on
p.supplierid = s.supplierid
where s.country = 'Germany'
order by s.companyname, p.productname
for update
This example is the same DECLARE keyword that you used on Day 11, "T-SQL Programming Fundamentals," to declare local variables. You can declare variables or declare a cursor with a single DECLARE statement, but you cannot do both. To declare both variables and cursors, you must use two DECLARE statements, as you will see in this example.
You must provide a name for the cursor in the DECLARE statement. After you have named the cursor, you would next specify the set of rows you want the cursor to have access to. This is defined as a regular SELECT statement, whether you intend to use the cursor to modify the rows or not. This cursor is defined as an UPDATE cursor because we intend to update the rows we are working with. If you wanted to just read the rows using the cursor, you would declare the cursor for read only instead of for update.
Tip
When naming a cursor, use a convention that you are comfortable with. Remember, I like to use crs_ as a prefix to the cursor name. I would use the name of the table in which most of the processing is being done. In the example, the cursor name is crs_products.
Note
If you do not specify a cursor type, either by design or by accident, it will default to READ_ONLY.
Opening the Cursor
After declaring the cursor, be sure that the statement is executed before the Open statement. When this has been done, you are ready to open the cursor. This is a very simple step, requiring a single line of code as shown:
Open Cursor crs_products
Declaring the Variables
In order for the cursor process to work properly, you must declare one variable for each column that is returned by the SELECT statement. The following shows the DECLARE statement for our example.
Declare @product_ID int, @product_name varchar(40),
@product_Price money, @product_country varchar(15),
@Supplier_ID int, @Supplier varchar(40),
@old_product_price Money, @tempstr varchar(60)
Besides declaring a unique variable for each of the columns being selected, we have also declared a second unit price variable to contain the old unit price. Finally, a temporary string variable is declared for use when printing the results.
Fetching the Rows
Now that you have set up the elements for using the cursor, you need to actually instruct the server to fetch a row of data for you. This is where the fun begins. The FETCH statement is the key to using cursors. The following shows the syntax for the FETCH statement:
Fetch
[[Next | Prior | First | Last |
Absolute { n | @n} |
Relative { n | @n} ]
from]
[Global] <cursor name> | <@cursor variable name>
[Into @variable1, @variable2, ...]
The only required elements are the actual word FETCH and the name of the cursor that you are using. The different keywords are described in Table 14.3.
If you use any of the FETCH keywords, you must also use the FROM clause. The last line of the syntax specifies where to store the data from the row being fetched. The number of variables must match the number of columns, and each variable must match in size and data type of the corresponding column from the selected column list.
The following code will fetch a row into the local variables that you have previously defined. You can also fetch the columns directly, which will print them to the screen just as a standard SELECT statement would. In this example, we will use the PRINT statement to send the results to the screen. Because the PRINT statement doesn't add column headers automatically, we will need to add them ourselves. Many cursor programs are used strictly for server-side processing and do not return rows the way this example does. As you can see, it is a lot less convenient than a simple SELECT statement.
Note
Although I should be printing everything that I selected, I am printing only the product name, the old unit price, and the new, updated unit price.
Caution
The following example will not execute by itself; several statements must be added, as you will see later in this section.
print 'Product Name Old Unit Price New Unit Price'
print '-------------------------------- -------------- --------------'
fetch next from crs_products into @product_ID, @product_name,
@product_Price, @product_Country, @Supplier_ID,
@Supplier
while @@fetch_status = 0 begin
set @old_product_price = @product_price
if @product_price > 40
set @product_price = @product_Price * 1.25
if @product_price is null
set @tempstr = convert(char(40), @product_name) +
convert(char(7),@old_product_price,1) + '--no pricing--'
else
set @tempstr = convert(char(40), @product_name) +
convert(char(7),@old_product_price,1) + convert(char(7),@product_price,1)
print @tempstr
fetch next from crs_products into @product_ID, @product_name,
@product_Price, @product_Country, @Supplier_ID,
@Supplier
end
After the headings are printed, you would execute the initial FETCH statement to retrieve the first row from the cursor keyset.
A cursor keyset refers to the set of rows that are accessed by the open cursor specified by the SELECT statement attached to the cursor.
You might have noticed a strange-looking variable that you did not declare. The @@FETCH_STATUS variable is really a function that reports on the status of the last FETCH statement. This status information should be used to determine the validity of the data returned by a FETCH statement prior to attempting any operation against that data. The @@FETCH_STATUS variable will always have one of three values as shown in Table 14.4.
| Value | Description |
|---|---|
| 0 | The most recent fetch statement succeeded. |
| -1 | The most recent fetch failed because there were no more rows to get. |
| -2 | The row requested no longer exists. |
The loop that was set up using the WHILE statement will continue to fetch rows until a –1 or –2 is returned by the @@FETCH_STATUS variable. Inside the loop, the original price of the product is saved in the variable @old_product_price before any action is taken. Then, the value of @product_price is tested. If the current row's price is greater than $40, it is increased by 25%. Next, the @product_price is tested, and, if the price is null, it will print a custom message rather than null values. Finally, the messages are all gathered together in a temporary string and printed to the screen. The next row is fetched, and the loop begins from the top.
Closing the Cursor
After all the rows have been processed, the WHILE loop will end, and you can now close the cursor that was being used. It is very simple to close a cursor as the following statement shows:
close crs_products
Closing the cursor will destroy the cursor keyset, meaning that it will wipe out the result set that the cursor was using. The cursor declaration will still be active, however, and can be reopened with a new OPEN statement after it has been closed. If you are using an insensitive or static cursor, you could close and then reopen the cursor in order to refresh the result set.
Destroying the Cursor
Finally, if the process is complete, you should destroy or deallocate the cursor. This is done using the following syntax:
deallocate crs_products
If you do not deallocate the cursor, memory will remain in use until your connection is closed. If you have declared a cursor, you cannot declare another with the same name until you have deallocated the original one. Listing 14.1 illustrates declaring, using, closing, and destroying a cursor.
Code Listing 14.1. Using Cursors in T-SQL Scripts
Product Name Old Unit Price New Unit Price -------------------------------- -------------- -------------- Gumbär Gummibärchen 31.23 31.23 NuNuCa Nuß-Nougat-Creme 14.00 14.00 Schoggi Schokolade 43.90 54.88 Nord-Ost Matjeshering 25.89 25.89 Original Frankfurter grüne Soße 13.00 13.00 Rhönbräu Klosterbier 7.75 7.75 Rössle Sauerkraut 45.60 57.00 Thüringer Rostbratwurst 123.79 154.74 Wimmers gute Semmelknödel 33.25 33.25
Other Cursor Functions
There are three main variables that are associated directly with the use of cursors:
You have already seen how to use the first cursor variable in the previous examples.
Using the @@CURSOR_ROWS Function
The @@CURSOR_ROWS function allows you to determine the number of rows that a cursor has retrieved at the time the function was called. This number can change because SQL Server can populate a large keyset and static cursors asynchronously to improve performance. To see how this really works, try executing the following code in Listing 14.2 to see the output that is generated.
Code Listing 14.2. Working with the @@Cursor Rows Function
Results:
Cursor Row Cnt -------------- 0 (1 row(s) affected) firstname ---------- Nancy (1 row(s) affected) Cursor Row Cnt -------------- -1 (1 row(s) affected)
Listing 14.2 declares a cursor and uses the SELECT statement to display the value of the @@CURSOR_ROWS function. The value of the function is 0 before the cursor is opened, and a value of –1 indicates that the cursor keyset is populated asynchronously. Table 14.5 lists the different return values of the function and the description of each.
Using the CURSOR_STATUS Function
The CURSOR_STATUS function is used in conjunction with a stored procedure. It allows you to check whether a cursor that was declared and opened in a stored procedure actually returned data in its keyset. You will see how to create and use stored procedures in the next few lessons. However, you will take a quick look at how this function works so that you will be able to use it in the future.
The syntax for the use of this function is
Cursor_Status
({ 'local', '<cursor name>'}
|{ 'global', '<cursor name>'}
|{ 'variable', '<cursor variable>'} )
The arguments of this function are listed in Table 14.6 along with their descriptions.
When this function is used, it returns a single value indicating the status of the cursor. Depending on whether the function is referencing a cursor or cursor variable, the values can have slightly different meanings. These values and their meanings are listed in Table 14.7.
Listing 14.3 shows how this function can be used. The first section of code creates a stored procedure (remember that you will learn how to do this tomorrow). Then, you will see how the actual code will make use of this stored procedure and the Cursor Status function.
Code Listing 14.3. Using the Cursor Status Function
Looking at Listing 14.3, you can see that the stored procedure is called in line 27. Then, when execution is returned, the following IF statement uses the Cursor_Status function to check whether the cursor variable '@my_lakes_cursor' has returned any data. Depending on the value in the Cursor Status function, your SQL code can perform the required actions.