Retrieving and Manipulating Data Through Cursors

Understanding the Purpose of a Cursor

The first time I heard that most SQL servers let you store and work with results set data in cursors, I wondered how much information could fit into a⅛-inch vertical (or horizontal) flashing line—and how one could possible view its contents. However, unlike a Windows or DOS cursor (which shows you the current insertion point for text in a document, field, or DOS command line), an SQL cursor is a temporary database object that either holds a copy of, or pointers to, rows of data stored in the system's permanent tables. A cursor gives you way to manipulate table data on a row-by-row basis instead of a result-set-at-a-time basis.

For example, if you want to give your customers a 1 percent rebate of their total orders placed during the period 12/01/00 to 12/31/00, you can execute an UPDATE statement similar to:

UPDATE customers SET rebate = (SELECT SUM(order_total) FROM orders WHERE order_date BETWEEN '12/01/2000' AND '12/31/2000' GROUP BY cust_no HAVING cust_no = cust_ID) * .01

The subquery in the current example generates a results set that lists the total amount purchased by each customer during the month of December 2000. The UPDATE statement then applies the 1 percent rebate to the orders as a group and places the REBATE due each customer into the customer's row in the CUSTOMERS table. Thus, SQL's standard way of working with data (in this case, each customer's December 2000 orders) a results set at a time is an efficient way of computing a rebate when the same percentage applies to each row of orders in the group.

Now, suppose you want to give rebates ranging from 1 percent to 2 percent, depending on how much a customer purchased during the same period. A customer is to receive a 1 percent rebate on the first $1,000.00 in purchases, 1.5 percent on the next $1,000.00 worth of orders, and 2 percent on additional purchase made after the customer's cumulative order total reaches $2,000.00. Although you still need to work with the group of orders each customer placed in December 2000, you can no longer apply the same percentage rebate to each row of orders in the results set. As such, a cursor is useful because it will let you work your way through each customer's orders one order at time. By keeping track of the cumulative amount purchased as you move from one order to the next, you can apply a different (higher) rebate percentage to orders placed as the pervious order total moves from one tier in the rebate table to the next.

You will learn how to create and work with cursors in Tips 427–444. For now, the important thing to understand is that a cursor is a temporary database object that lets you work with the data in its underlying table(s) one row at a time. When you need to perform multiple actions on the same set of data, using a cursor to hold the data while you work with it is more efficient than executing the same query multiple times—once for each of the actions you need to take on the results set.

Using the DECLARE CURSOR Statement to Define a Cursor

An SQL DECLARE CURSOR statement lets you specify not only the query that will be used to generate the results set (the set of rows) that the DBMS will store in a cursor, but also the actions a user (or an application) can take while working with the cursor. When you declare a cursor by executing a DECLARE CURSOR statement such as

DECLARE cur_payroll_work CURSOR FOR SELECT emp_num, dept, hourly_rate, ot_rate, monthly_salary, time_in, time_out, project, tcard_hourly_rate, tcard_labor_cost FROM timecards, employees WHERE timecards.emp_ID = employees.emp_num

the SQL server validates the cursor's query, making sure it is syntactically correct and that tables or views listed in the SELECT statement's FROM clause do indeed exist in the database.

The syntax of the DECLARE CURSOR statement is

DECLARE [INSENSITIVE][SCROLL] CURSOR FOR [FOR {READ ONLY | UPDATE[OF ]}] where: INSENSITIVE tells the DBMS to make a temporary copy of the query results set data (vs. using pointers that reference columns in rows of "live" data in permanent database tables). If you are using an (UPDATE and DELETE) INSENSITIVE cursor, any changes made to the underlying tables will not be reflected in the cursor's data. Moreover, an INSENSITIVE cursor is READ ONLY, which means you cannot modify its contents or use an INSENSITIVE cursor to modify the contents of its underlying (base) tables. If the INSENSITIVE option is omitted from the DECLARE CURSOR statement, the DBMS will create an UPDATE and DELETE sensitive cursor that reflects any changes made to its underlying rows in the permanent database table(s). SCROLL specifies that the cursor is to support the selection of any of its rows as the current row by using any of the FETCH options (FIRST, LAST, PRIOR, NEXT, RELATIVE, and ABSOLUTE). If the SCROLL option is omitted from the DECLARE CURSOR statement, the cursor will support only single-row forward movement through its rows (that is, it will support only the FETCH NEXT option for moving through the cursor). READ ONLY prevents the user (or application) using the cursor from changing the cursor's contents by updating data values or deleting rows. Consequently, you cannot use a READ ONLY cursor to modify data in the cursor's underlying table(s). If READ ONLY is omitted from the DECLARE CURSOR statement, the DBMS will create a cursor that can be used to modify its base table(s). UPDATE is the UPDATE clause that tells the DBMS to create an updateable cursor and (optionally) lists the cursor columns whose values can be updated. If any columns are listed in the UPDATE, only the columns listed are updateable. On the other hand, if the DECLARE CURSOR statement specifies only the UPDATE option (without a column list), then the cursor will allow updates to any or all of its columns. Therefore, the DECLARE CURSOR statement at the beginning of this tip defines (declares) a "forward only" cursor named PAYROLL_WORK, which displays all of the columns and rows in the TIMECARDS table and some of the columns values from rows in the EMPLOYEES table. 428 Using an OPEN Statement to Create a Cursor When executing a DECLARE CURSOR statement, the DBMS only validates the cursor's SELECT statement. Conversely, when executing an OPEN statement, the DBMS not only creates the cursor, but it also populates (fills) it with data by executing the cursor's SELECT statement. If the DECLARE CURSOR statement includes the INSENSITIVE option, the OPEN statement will cause the DBMS to create a temporary table to hold the query's results set. Otherwise, the DBMS will place the row ID for each of the rows in the cursor query's results set into a temporary workspace managed by the cursor. They syntax of the (cursor) OPEN statement is: OPEN As such, to create and populate the cursor created at the beginning of Tip 427, "Using the DECLARE CURSOR Statement to Define a Cursor," execute the OPEN statement: OPEN cur_payroll_work Because the declaration for the cursor in the current example does not include the INSENSITIVE option, the DBMS will fill the cursor's storage structure with the row ID of each of the rows in the underlying table(s) (listed in the cursor query's FROM clause) that satisfy the search criteria in the cursor query's WHERE clause. (If the DECLARE CURSOR statement had included the INSENSITIVE option, the DBMS would have created a table and filled it with a copy of the data from the corresponding rows in the cursor's underlying table[s].) Note  After you execute the OPEN statement to create and populate the cursor, you can use the @@CURSOR_ROWS function to retrieve the number of rows in the most recently opened cursor. 429 Using the ORDER BY Clause to Change the Order of the Rows in a Cursor If you need to process cursor data in a particular order, you can have the DBMS sort the cursor data for you by adding an ORDER BY clause to the query in the DECLARE CURSOR statement. For example, if you are working on a payroll system and have a cursor that contains weekly timecard data, you will want the time cards sorted in order by date within employee number. By grouping all of the timecards for the same employee together in date order, you can make one pass through the rows in the cursor to total the number of hours the employee worked during the period and compute the employee's rate of pay for each timecard. As soon as the total hours worked exceeds 40 for the week, you know to switch to the overtime rate as you compute the hourly wage for the employee's remaining timecards. Then, as soon as you FETCH a timecard that has a different employee number than the one you just processed, you know to reset the total hours to the number of hours in the new time-card since you have moved on to the next employee. The syntax of the ORDER BY clause used to set the order of the rows in a cursor is the same as the ORDER BY clause in a normal query: ORDER BY [ASC | DESC] [,... [ASC | DESC]] Thus, to sort the TIMECARDS in the CUR_PAYROLL_WORK cursor (declared in Tip 427, "Using the DECLARE CURSOR Statement to Define a Cursor") in ascending order by date and time within employee ID, change the cursor's definition to: DECLARE cur_payroll_work CURSOR FOR SELECT emp_num, dept, hourly_rate, ot_rate, monthly_salary, time_in, time_out, project, tcard_hourly_rate, tcard_labor_cost FROM timecards, employees WHERE timecards.emp_ID = employees.emp_num ORDER BY emp_ID, time_in, time_out Since the default sort order is ascending, you do not have to include the ASC option after any of the columns listed in the ORDER BY clause to sort the query results table in ascending order. However, if you want the rows sorted in descending order by any of the columns named in the column list, you must include the DESC option after the column name. Note  Unlike the ORDER BY clause in noncursor SELECT statements, only the columns listed for display in the query's SELECT clause can appear as columns in the ORDER BY clause. (In noncursor SELECT statements, any column in the table[s] listed in the query's FROM clause may appear in the ORDER BY clause—even if the columns are not listed [for display] in the statement's SELECT clause.) 430 Including Calculated Values as Columns in a Cursor In addition to columns from tables and views listed in the cursor SELECT statement's FROM clause, a cursor can include computed columns. Suppose, for example, that some of the employees in a company are paid a monthly salary instead of an hourly wage. However, for the purposes of computing a labor cost for each of the company's projects, the salaried employees submit timesheets showing the time worked on each project, and an "hourly rate" is computed according to the formula: ( * 12) / (40 hours * 52 weeks) If the HOURLY_RATE column is NULL for salaried employees and the MONTHLY_ SALARY column is NULL for hourly employees, then you can use the COALESCE function to add a computed HRLY_PAYRATE column to the CUR_PAYROLL_WORK cursor by changing the DECLARE CURSOR statement to: DECLARE cur_payroll_work CURSOR FOR SELECT emp_num, dept, ot_rate, monthly_salary, time_in, time_out, project, tcard_hourly_rate, tcard_labor_cost, COALESCE(hourly_rate, (monthly_salary * 12) / 2080) hrly_rate (CONVERT(REAL,(time_out - time_in),8) * 24) hours_worked FROM timecards, employees WHERE timecards.emp_ID = employees.emp_num ORDER BY emp_ID, time_in, time_out (In addition to the HRLY_RATE column, the cursor in the current example includes the computed column HOURS_WORKED, to avoid having to compute the amount of time worked on each timecard when processing the cursor's data.) Note  Be sure to name each of the computed columns so that it will be easy to refer to all of the columns in the cursor as necessary when you process the data in the cursor 431 Using the FOR UPDATE Clause to Specify Which Underlying Table Columns a Cursor Can Modify By default, an updateable cursor will let you UPDATE the values in all of the columns listed in the SELECT clause of the query used to define the cursor. Moreover, the cursor will let you use it to DELETE one row at a time any of its underlying tables. If you want to prevent a user from being able to use a cursor to modify column values in or remove rows from its underlying (base) table(s), add the READ ONLY clause to the DECLARE CURSOR statement, as in: DECLARE CURSOR FOR READ ONLY

Conversely, if you omit both the READ ONLY clause and the FOR UPDATE clause from a DECLARE CURSOR statement, or include a FOR UPDATE clause without a column list, the user will be able to use the cursor to UPDATE the value of any column listed in the cursor's SELECT statement. Then, by executing an UPDATE or DELETE statement with a WHERE CURRENT OF clause, the user can use the cursor to make changes in or remove a row from the cursor's underlying table. (You will learn about positioned updates and deletes in Tip 434, "Understanding Cursor-based Positioned DELETE Statements," and Tip 435, "Understanding Cursor-based Positioned UPDATE Statements.")

Thus, the DECLARE CURSOR statements

DECLARE CURSOR FOR and DECLARE CURSOR FOR FOR UPDATE

will allow the user to UPDATE any column listed the SELECT clause of the cursor's SELECT statement. To limit the user to being able to update values in only some of the cursor's columns, list the columns eligible for update in the FOR UPDATE clause of the DECLARE CURSOR statement.

For example, listing the two columns in the FOR UPDATE clause of the DECLARE CURSOR statement

DECLARE cur_payroll_work CURSOR FOR SELECT emp_num, dept, ot_rate, monthly_salary, time_in, time_out, project, tcard_hourly_rate, tcard_labor_cost, COALESCE(hourly_rate, (monthly_salary * 12) / 2080) hrly_rate (CONVERT(REAL,(time_out - time_in),8) * 24) hours_worked FROM timecards, employees WHERE timecards.emp_ID = employees.emp_num FOR UPDATE OF tcard_hourly_rate, tcard_labor_cost

will let a user use the cursor to UPDATE values in only the TCARD_HOURLY_RATE and TCARD_LABOR_COST columns of the cursor and the TIMECARDS table.

Using a FETCH Statement to Retrieve Column Values from a Row in the Cursor

A FETCH statement lets you retrieve or display data values from the next row in the cursor. When you execute an OPEN (cursor) statement, the DBMS opens the cursor whose name follows the keyword OPEN by populating (filling) the cursor with the results set from the cursor's SELECT statement and positioning the cursor's current row pointer prior to the first row in the cursor. Therefore, when the first FETCH statement you execute after opening a cursor retrieves data from the "next" row in the cursor, it will return the data values in the cursor's first row.

The syntax of the FETCH statement is:

FETCH [NEXT | PRIOR | FIRST | LAST | {ABSOLUTE }|{(RELATIVE }] FROM [INTO [,...]]

Therefore, to display the IDs and names of the first two authors in the CU_AUTH cursor declared by

USE pubs DECLARE cur_authors CURSOR FOR SELECT au_ID, au_fname, au_lname FROM authors

you could execute the Transact-SQL code

DECLARE @au_ID CHAR(11), @au_fname VARCHAR(30), @au_lname VARCHAR(30) OPEN cur_authors FETCH cur_authors INTO @au_ID, @au_fname, @au_lname PRINT 'ID: ' + @au_ID+' Name: '+@au_fname +' '+@au_lname

which will produce output similar to:

ID: 172-32-1176 Name: Johnson White ID: 213-46-8915 Name: Marjorie Green

Each time you execute a FETCH statement, the DBMS moves to the next row in the cursor and fetches (copies) the values in the cursor's columns into the temporary variables listed in the statement's INTO clause. The FETCH statement works its way through the cursor columns and the variables in its INTO clause from left to right, copying the value in the cursor column into the corresponding variable in the variable list. As such, the variable list in the FETCH statement's INTO clause must match the column list in the cursor's SELECT statement both in number and in data type. (If a FETCH statement has no INTO clause, then the DBMS will display the values in all of the columns of the cursor's current row to the screen.)

Notice that if the FETCH statement does not include a row selection (ABSOLUTE, RELATIVE, FIRST, LAST) or movement direction (NEXT, PRIOR), the DBMS executes the statement as FETCH NEXT.

Orienting the Cursor s Current Row Pointer Prior to a Fetching Column Values from the Current Row

As you saw in Tip 432, "Using a FETCH Statement to Retrieve Column Values from a Row in the Cursor," if you execute a FETCH statement that does not include a direction for movement through the cursor, such as

FETCH FROM INTO

the DBMS assumes you want to execute a FETCH NEXT. As such, the system moves the cursor's row pointer from its current location forward one row in the cursor and then has the FETCH statement retrieve the column values from that row into the variables in the statement's INTO clause-or display them to the screen, if there is no INTO clause. If you do not include the SCROLL option when declaring a cursor, the DBMS will allow you only to move forward one row at a time through the cursor, using the FETCH NEXT statement.

You can use the FETCH statement's row positioning options (other than NEXT) only if you are working with a SCROLL cursor. To declare a SCROLL cursor, include the keyword SCROLL in the cursor's declaration, as in:

DECLARE SCROLL CURSOR FOR When working with a SCROLL cursor, you can use any of the FETCH statement's cursor row pointer positioning options to move to a specific row in the cursor prior to retrieving its column values into temporary variables (or to the screen). The FETCH statement's row positioning options are: NEXT. Move forward one row. PRIOR. Move backward one row. FIRST. Move to the first row in the results set. LAST. Move to the last row in the results set. ABSOLUTE n. Move to the nth row in the results set. If n is a positive number, the DBMS moves forward to the nth row from the top of the cursor. If n is negative, the DBMS moves to the nth from the bottom of the results set. RELATIVE n. Move n rows from the current position of the row pointer. If n is positive, the DBMS moves the row pointer n rows forward. If n is negative, the DBMS moves the row pointer n rows backward (toward the top of the cursor). Therefore, if you are working with a cursor declared as DECLARE cur_authors SCROLL CURSOR FOR SELECT au_fname, au_lname FROM authors executing the FETCH statement FETCH RELATIVE 1 FROM cur_authors INTO @au_fname, @au_lname has the same effect as FETCH NEXT FROM cur_authors INTO @au_fname, @au_lname in that both statements move the cursor's current row pointer forward one row before retrieving the column values in the cursor's (new) current row. Similarly, the FETCH statement FETCH RELATIVE -1 FROM cur_authors INTO @au_fname, @au_lname has the same effect as FETCH PRIOR FROM cur_authors INTO @au_fname, @au_lname in that both statements move the cursor's current row pointer backward (toward the first row in the cursor) one row from the current row. Now, suppose the cursor's current row pointer is on row 5 of a 20-row cursor. The FETCH statement FETCH RELATIVE 10 FROM cur_authors INTO @au_fname, @au_lname will move the current row pointer forward 10 rows to row 15, while the FETCH statement FETCH ABSOLUTE 10 FROM cur_authors INTO @au_fname, @au_lname will move the current row pointer to cursor row 10. Similarly, the FETCH statement FETCH RELATIVE -3 FROM cur_authors INTO @au_fname, @au_lname will move the cursor's current row pointer to cursor row 2 (which is three rows up [toward the top of the cursor]) from current row 5; the FETCH statement FETCH ABSOLUTE -3 FROM cur_authors INTO @au_fname, @au_lname will move the cursor's current row pointer to cursor row 17, which is three rows back from the bottom of the cursor (row 20). 434 Understanding Cursor based Positioned DELETE Statements If a cursor is updateable (that is, the DECLARE CURSOR statement used to declare the cursor does not include a READ ONLY clause), you can use the cursor to DELETE a row from the table from which the cursor's data was derived. A cursor-based DELETE is called a positioned DELETE because you tell the DBMS to remove a row from the table based on the current position of the cursor's row pointer. The WHERE clause in a standard (searched) DELETE statement describes the rows to be deleted based on the value in one or more of their columns. For example, the searched DELETE statement DELETE FROM orders WHERE date_shipped = NULL AND order_date < GetDate() - 30 tells the DBMS to search for and DELETE any rows in which the DATE_SHIPPED is NULL and the ORDER_DATE is more than 30 days prior to the current date. A positioned DELETE, on the other hand, tells the server to DELETE the row in the underlying (base) table associated with the cursor's current row, as in: DELETE FROM WHERE CURRENT OF Thus, to remove the third row in the cursor and DELETE the row from the cursor's underlying table, you would first execute a FETCH statement to make the third row the cursor's current row, and then execute a positioned DELETE. For example, to remove the third row from the cursor CU_TIMECARDS and the row from which the third row in the cursor was derived, you would execute a statement batch similar to: OPEN cur_timecards CURSOR FETCH FROM cur_timecards FETCH FROM cur_timecards FETCH FROM cur_timecards DELETE FROM timecards WHERE CURRENT OF cur_timecards Note  Be sure to check the effect of the various DECLARE CURSOR clauses on a cursor's READ ONLY status for your DBMS. MS-SQL Server, for example, forces a SCROLL cursor to be READ ONLY. MS-SQL Server does, however, allow you to add a DYNAMIC clause to the DECLARE cursor statement to create a scrollable cursor that is also updateable. 435 Understanding Cursor based Positioned UPDATE Statements In addition to executing positioned DELETE statements, you can use an updateable cursor to perform positioned updates. A positioned UPDATE lets you set column values in the underlying (base) table row from which the current row in the cursor was derived. Thus, a positioned UPDATE is similar to a positioned DELETE because the search criteria in its WHERE clause is based on the position of a cursor's current row pointer instead of the value in one or more of the columns in a row. The WHERE clause in a standard (searched) UPDATE statement describes the rows to be modified based on the value in one or more columns. For example, the searched UPDATE statement UPDATE employees SET ot_payrate = 1.5 WHERE monthly_salary IS NULL tells the DBMS to search the EMPLOYEES table for rows in which the MONTHLY_SALARY column is NULL, and then set the value of the OT_PAYRATE column in those rows to 1.5. A positioned update, meanwhile, uses the syntax UPDATE SET = [,.,. = ] WHERE CURRENT OF to tell the DBMS to UPDATE values in one or more columns of the row in the underlying (base) table that corresponds with the current row in the cursor. For example, the SQL statement batch OPEN cur_payroll_work CURSOR FETCH FROM cur_payroll_work FETCH FROM cur_payroll_work INTO @emp_num, @dept, @ot_rate, @time_in, @time_out, @project, @tcard_hourly_rate, @tcard_labor_cost, @hrly_payrate, @hours_worked UPDATE timecards SET tcard_hourly_rate = @hrly_payrate, tcard_labor_cost = @hrly_payrate * @hours_worked WHERE CURRENT OF cur_timecards will set the value of the columns TCARD_HOURLY_RATE and TCARD_LABOR_COST in the TIMECARDS table row that generated the second (current) row in the CUR_PAY-ROLL_WORK cursor. 436 Using an Index to Change the Order of the Rows in a Cursor In Tip 429, "Using the ORDER BY Clause to Change the Order of the Rows in a Cursor," you learned that you can add an ORDER BY clause to a cursor's SELECT statement to sort the rows in a cursor by the value in one or more of its columns. Unfortunately, some DBMS implementations, including MS-SQL Server, allow you to use an ORDER BY clause only in the SELECT statement of a READ ONLY cursor. Therefore, if you add an ORDER BY clause to the SELECT statement of a cursor on an MS-SQL Server, you will not be able to use the cursor to execute positioned DELETE or positioned UPDATE statements. If you need an updateable cursor with sorted data, create an index on the cursor's base table(s) and tell the DBMS to use the index when populating the cursor. For example, suppose you have a set of timecards you want sorted by date and time within the employee ID. If you use the CREATE INDEX statement syntax CREATE [UNIQUE][CLUSTERED |NONCLUSTERED] INDEX ON ([< column name>[, . . .]) to create an index such as CREATE INDEX tc_emp_ID_date ON timecards (emp_ID, time_in, time_out) on the TIMECARDS table, you can add a WITH INDEX clause to the FROM clause in the cursor's SELECT statement DECLARE cur_payroll_work CURSOR FOR SELECT emp_num, dept, ot_rate, time_in, time_out, project, tcard_hourly_rate, tcard_labor_cost, COALESCE(hourly_rate, (monthly_salary * 12) / 2080) hrly_payrate, (CONVERT(REAL(time_out - time_in),8>* 24) hours_worked FROM timecards WITH (INDEX(tc_emp_ID_date)), employees WHERE timecards.emp_ID = employees.emp_ID FOR UPDATE OF tccard_hourly_rate, tcard.labor_cost to create an updateable cursor whose rows are sorted (in the order of the index TC_EMP_ID_DATE). Without an ORDER BY clause in its DECLARE CURSOR statement, the DBMS will leave the rows in a cursor arranged in the order in which the system added them when it populated the cursor (in response to an OPEN [cursor] statement). The WITH INDEX clause in the SELECT statement's FROM clause tells the DBMS to retrieve TIMECARD rows for the cursor in order by EMP_IN/TIME_IN/TIME_OUT. 437 Using @@FETCH_STATUS to Work Through the Rows in a Cursor with a WHILE Loop @@FETCH_STATUS is an MS-SQL Server system-defined global variable that contains the execution status (the result code) of the most recently executed FETCH statement. An @@FETCH_STATUS value of zero (0) indicates that the DBMS successfully executed the most recent FETCH statement. Any other value indicates that the FETCH was unsuccessful. As such, you can use the value of @@FETCH_STATUS as the termination test in a Transact-SQL WHILE loop to work your way through the rows in a cursor. For example, if you execute the statement batch SET NOCOUNT ON DECLARE cur_authors CURSOR FOR SELECT * FROM pubs.dbo.authors -DECLARE temporary variables here OPEN cur_authors FETCH FROM cur_authors WHILE @@FETCH_STATUS = 0 BEGIN -Add statements to process individual rows of the cursor -here -A FETCH statement without an INTO clause tells the DBMS -to display the contents of the cursor row's columns to -the screen. -If you are processing the rows in a cursor, you will -normally FETCH cursor row column values INTO temporary -variables vs. just displaying them. FETCH FROM cur_authors END DEALLOCATE cur_authors SET NOCOUNT OFF the MS-SQL Server will populate the CUR_AUTHORS cursor with rows from the AUTHORS table in the PUBS database. Then the DBMS will FETCH and display the column values in each row of the cursor until the @@FETCH_STATUS is no longer zero (0). A nonzero @@FETCH_STATUS indicates that the DBMS was not able to FETCH another row from the cursor, which terminates the loop under the assumption that there are no more rows left to retrieve. Notice that the current example includes a FETCH statement prior to the WHILE loop. This is because the value of @@FETCH_STATUS is undefined before the connection has executed its first FETCH statement. Moreover, the system does not set the value of @@FETCH_STATUS to zero (0) when you populate a cursor by executing an OPEN statement. As such, you must execute one FETCH to set the value of @@FETCH_STATUS before testing its value in a WHILE loop. Note  @@FETCH_STATUS is global to all cursors used by a connection. As such, if you execute a FETCH statement on one cursor and then call a stored procedure that opens and fetches rows from another cursor, the value of @@FETCH_STATUS will reflect the execution status of the last FETCH executed in the stored procedure, not the FETCH executed before the stored procedure was called. 438 Understanding How to Set Cursor Sensitivity to Changes in Underlying Tables As you learned from the discussion of cursors in Tips 426-436, a cursor's SELECT statement determines which table rows (and columns) to include in the cursor. Even if you declare a cursor as READ ONLY, the contents of its underlying table(s) may change between the time the cursor is populated (by executing its OPEN statement) and the time it is closed (by executing a CLOSE or a DEALLOCATE statement). When working with cursors, you must decide whether or not you want the actions of other users to change the contents of the cursor after the DBMS populates it by executing the cursor's SELECT statement. Suppose, for example, that you DECLARE and OPEN a cursor as: DECLARE cur_employees CURSOR FOR SELECT * FROM employees WHERE hrly_rate < 10.00 AND status = 'Active' OPEN cur_employees If someone changes the hourly rate of one of the employees from $9.50 to $9.75, should the cursor continue to display the row's contents as if nothing had happened? Now, suppose someone deletes an active employee making $8.75 per hour from the EMPLOYEES table, changes an employee's status from active to terminated, or changes an HRLY_RATE from $9.50 to $10.25. Should the cursor continue to display a row if the corresponding row has been deleted from the underlying table or if the underlying row's column values no longer satisfy the search criteria in the cursor's SELECT statement? The answer to both questions is a definite "Maybe"-depending on the purpose of the cursor. If you need a "snapshot" of the average salary and count of employees making less than $10 per hour at a particular point in time, you would want the cursor to remain static and ignore any changes made to its underlying table(s). On the other hand, if you are trying to generate an "up-to-the-minute" report of existing employees, you will want the cursor to reflect new additions and to remove rows that no longer meet your criteria as you work your way through the rows in the cursor. Fortunately, SQL lets you decide whether or not a cursor is to reflect changes made after the DBMS opens and populates it. To make a cursor insensitive to modifications made to its underlying (base) table(s), add the INSENSITIVE clause to the cursor's definition, as in: DECLARE cur_employees INSENSITIVE CURSOR FOR SELECT * FROM employees WHERE hrly_rate < 10.00 AND status = 'Active' OPEN cur_employees If a cursor is INSENSITIVE, the DBMS will store a copy of the cursor's underlying data in a temporary table. Any changes made to the underlying (base) table will not be reflected in the cursor because the cursor will FETCH data values from the temporary, static table. On the other hand, if you omit the INSENSITIVE clause (as shown in the current tip's first example), the cursor will reflect the results of any committed DELETE and UPDATE statements that modify the contents of the cursor's underlying (base) table(s). 439 Using the CLOSE Statement to Close a Cursor After you finish working with a cursor, CLOSE the cursor to free up the system resources (such as hard disk space and server memory) used by the cursor and to release any locks on rows (or memory pages) currently held by the cursor. The syntax of the CLOSE (cursor) statement is CLOSE and executing it will, conceptually, DROP the table of query results created when the DBMS opened the cursor with: OPEN Unlike the DBLIB buffer, you do not have to FETCH and process all of the rows in a cursor before you CLOSE it. Just bear in mind that any rows left unprocessed when you close a cursor are no longer available to any DBMS user, external application, or stored procedure. As mentioned at the beginning of the current tip, the CLOSE statement frees up the storage space used by the cursor's pointers to rows in its underlying (base) table(s) or by the temporary table that holds a copy of the underlying data for INSENSITIVE and STATIC cursors. However, the CLOSE statement does not remove the cursor's structure from the database. As such, the cursor itself still exists as a database object—even after you CLOSE the cursor. After closing a cursor, you need only execute another OPEN statement to repopulate it. Then you can begin processing the rows in the cursor rows from the beginning again. (You need not [and, indeed, cannot] redeclare the same cursor if you have only closed [vs. deallocated] it.) Note  If you declare an INSENSITIVE or (MS-SQL Server) STATIC cursor, you can CLOSE and then OPEN the cursor again to have the cursor's contents reflect any changes made to its underlying tables since the cursor was opened the first time. Closing a cursor discards all of the rows of data values (or pointers) from the cursor. Reopening the cursor tells the DBMS to re-execute the cursor's SELECT statement to repopulate (refill) the cursor with data that now meets the cursor's search criteria. 440 Using the DEALLOCATE Statement to Drop a Cursor and Free Its Server Resources If you are finished using a cursor and do not plan to repopulate it by reopening it during the current session, execute a DEALLOCATE statement to both CLOSE the cursor and remove its structure and definition from the database. Unlike the CLOSE statement, which leaves the cursor's definition and structure in the DBMS system tables, the DEALLOCATE statement not removes any data in the cursor (by closing the cursor), but also removes the cursor as an object from the database. As such, if you do plan to reuse the cursor, execute a CLOSE statement instead of a DEALLOCATE (cursor) statement to avoid the overhead of having to execute another DECLARE CURSOR statement before executing an OPEN statement to reopen the same cursor later. The syntax of a DEALLOCATE statement is DEALLOCATE and the cursor need not be closed prior to being deallocated—the DEALLOCATE will take care of both actions, closing the cursor (if it is still open) and removing the cursor as a database object. Note  Deallocating a SCROLL cursor frees any scroll locks the cursor holds to protect its data in underlying tables from UPDATE or DELETE statements executed by other users. However, if the cursor was declared and opened within a transaction, deallocating the cursor does not release any locks the (still) open transaction has on the cursor's underlying table(s). Open transaction locks are released only when you close the transaction by executing a COMMIT statement (to keep the changes made) or a ROLLBACK (to undo any work performed by the transaction). 441 Understanding the Transact SQL Extended Syntax for the DECLARE CURSOR Statement MS-SQL Server supports two forms of the DECLARE CURSOR statement. You can declare a cursor using the standard SQL syntax DECLARE [INSENSITIVE] [SCROLL] CURSOR FOR [FOR {READ ONLY|UPDATE [OF ]}]

(which you learned about in Tip 427, "Using the DECLARE CURSOR Statement to Define a Cursor"), or you can use the Transact-SQL Extended syntax

DECLARE CURSOR [GLOBAL I LOCAL] [FORWARD_ONLY|SCROLL][STATIC | KEYSET | DYNAMIC | FAST_FORWARD] [READ_QNLY|SCROLL_LOCKS|OPTOMISTIC][TYPE_WARNING] FOR [FOR UPDATE[OF ]}] where: GLOBAL. Specifies that the scope of a cursor is global to the connection. If a cursor has a global scope, it can be referenced in any SQL statement on the connection after the cursor is declared. (By default, a cursor normally has a local scope, which means it exists as a database object only with the statement batch, stored procedure, or trigger in which it is declared.) The DBMS will automatically DEALLOCATE a GLOBAL cursor only when the connection on which it was created disconnects from the server. LOCAL. Specifies that a cursor can be referenced only by statements within the same statement batch, stored procedure, or trigger in which the cursor was defined. (You will learn about triggers in Tip 448, "Understanding Triggers.") The DBMS automatically deallocates a LOCAL cursor after it executes the last statement in a statement batch, trigger, or stored procedure—unless the stored procedure passes the cursor to the caller in an OUTPUT parameter. If passed as an OUTPUT parameter, the system will DEALLOCATE the cursor when the last variable that references the cursor goes out of scope. FORWARD_ONLY. Specifies that you can use the FETCH NEXT statement only to move forward through the cursor, one row at a time. STATIC. Is the Transact-SQL equivalent of the standard DECLARE CURSOR statement's INSENSITIVE setting. If a cursor is STATIC (or INSENSITIVE), the DBMS makes a temporary copy of the data in the cursor's underlying rows in a table in the TEMPDB database. A STATIC cursor cannot be used with a WHERE CURRENT OF clause to execute a positioned UPDATE or DELETE. Moreover, a STATIC cursor will not reflect any changes made to its underlying table(s) after the DBMS populates the cursor. KEYSET. Specifies that the rows and the order of the rows in a cursor are set when the DBMS opens the cursor and populates it. The KEYSET is a table in the TEMPDB database that contains a set of keys that uniquely identify each row in the cursor. A KEYSET cursor will reflect any committed changes made to nonkey column values. Moreover, if a row referenced is removed from the cursor's underlying table, a subsequent FETCH of the same row in the cursor will return an @@FETCH_STATUS of -2. Any row inserted into a KEYSET cursor's underlying table will not be added to the cursor—even if the new row's column values satisfy the search criteria in the cursor's SELECT statement. Any changes made to key columns in the KEYSET cursor's underlying table(s) are treated as a DELETE of the original row followed by an INSERT of a new row. As such, UPDATE statements that change the values in key columns in the underlying table will cause the corresponding row in the KEYSET cursor to "disappear" as if it had been deleted. Any changes to key (and nonkey columns) made through the cursor (with a positioned UPDATE) remain visible in the cursor. DYNAMIC. Specifies that the cursor is to reflect any changes made to its underlying table(s) by committed UPDATE, DELETE, and INSERT statements. As such, the DBMS will add new row pointers to the cursor as rows that satisfy the cursor's search criteria are added to the cursor's underlying table(s). Conversely, the DBMS will remove row pointers from the cursor as corresponding rows are deleted from the underlying table or if their column values are changed such that they no longer satisfy the cursor's search criteria. Because the rows in a DYNAMIC cursor can change prior to each FETCH, a DYNAMIC cursor does not support the FETCH ABSOLUTE statement. FAST_FORWARD. Specifies that the DBMS is to optimize the cursor for performance as a READ_ONLY, FORWARD_ONLY cursor. SCROLL_LOCKS. Tells the DBMS to place a lock on each row in the underlying table(s) as the server adds the row to the cursor. The SCROLL_LOCKS option is used to guarantee that positioned UPDATE and DELETE statements made through the cursor will succeed. OPTIMISTIC. Specifies that any positioned UPDATE or DELETE made through the cursor will fail if the target row was updated after it was added to the cursor. Unlike the SCROLL_LOCKS option, the OPTIMISTIC option does not tell the DBMS to place a lock on each underlying table row it puts into the cursor. Instead, when determining whether or not to allow a positioned UPDATE or DELETE, the DBMS checks the value of the underlying row's TIMESTAMP column (or its checksum, if the row has no column of data type TIMESTAMP) to determine if the row was changed after it was added to the cursor. TYPE_WARNING. Specifies that the DBMS is to send a warning message to the client if the cursor's declaration contains conflicting options that will implicitly convert the cursor from the requested type to another. 442 Understanding Asynchronous KEYSET Cursor Population When you use the Transact-SQL DECLARE CURSOR syntax (which you learned about in Tip 441, "Understanding the Transact-SQL Extended Syntax for the DECLARE CURSOR Statement") to declare a STATIC or KEYSET cursor, you can tell the MS-SQL Server to populate the cursor asynchronously. Both STATIC and KEYSET cursors create a work table in the TEMPDB database. While a KEYSET cursor uses the work table to store the keys that identify the cursor's rows in its underlying table(s), a STATIC cursor stores a copy of its underlying rows in the table. When the DBMS populates a STATIC or KEYSET cursor synchronously, the server takes control of the session until it has filled the cursor's work table with all of the rows or keys returned by the cursor's SELECT statement. If the query optimizer estimates that a STATIC or KEYSET cursor's query will return more rows than the value in the server's CURSOR THRESHOLD setting, the server will partially populate the work table, start another thread to finish generating the cursor's result set, and return control to the user or application that submitted the cursor's OPEN statement. As a result, the user (or application) can start fetching the first rows in an asynchronously populated cursor without having to wait until the DBMS retrieves the entire results set into the cursor before performing the first FETCH. To set the value of the CURSOR THRESHOLD, use the SP_CONFIGURE system stored procedure. Then use the Transact-SQL RECONFIGURE command to tell the query optimizer to start using the new value. For example, to have the optimizer populate cursors with more than 500 rows asynchronously, execute the statement batch: EXEC SP_CONFIGURE 'CURSOR THRESHOLD', 500 RECONFIGURE When you set the CURSOR THRESHOLD to -1, the DBMS will populate all cursors synchronously. Conversely, a CURSOR THRESHOLD of 0 tells the server to populate all cursors asynchronously. Set the CURSOR THRESHOLD to any other value, and the optimizer will compare the number of rows it expects the cursor's query to return to the value of CURSOR THRESHOLD. If the number of expected rows exceeds the value of CURSOR THRESHOLD, the DBMS will populate the cursor asynchronously. Otherwise, the server will populate it synchronously. Note  There is extra overhead associated with populating a cursor asynchronously. As such, it is more efficient to populate small cursors synchronously. Therefore, make the value of the CURSOR THRESHOLD parameter large enough that the server will use asynchronous cursor population only for cursors with a SELECT statement that returns a large number of rows. 443 Using the @@CURSOR_ROWS System Variable to Determine the Number of Rows in a Cursor When an MS-SQL Server populates any type of cursor other than a DYNAMIC cursor, the DBMS sets the value of @@CURSOR_ROWS to the number of rows in the results table generated by the cursor's SELECT statement. For example, if the SELECT statement SELECT * FROM pubs.dbo.authors generates a results set with 23 rows of data, then executing the statement batch DECLARE cur_authors SCROLL CURSOR FOR SELECT * FROM pubs.dbo.authors PRINT 'There are ' + CAST(@@CURSOR_ROWS AS VARCHAR(6) + ' rows in the cursor.' will display the message: There are 23 rows in the cursor. Bear in mind that @@CURSOR_ROWS is a global system variable whose value is set by the last cursor opened on a connection as defined in Table 443.1. Table 443.1: @@CURSOR_ROWS Values and Descriptions @@CURSOR_ROWS Description - If the cursor is populated asynchronously, the value of @@CURSOR_ROWS will be a negative number that represents the number of rows currently in the work table. For example, an @@CURSOR_ROWS value of -758 tells you that the system is still populating an asynchronous cursor that contains 758 rows thus far. Whether populated asynchronously or synchronously, @@CURSOR_ROWS will contain the number of rows in the cursor as a positive value as soon as the DBMS is finished populating the cursor. -1 If the cursor is DYNAMIC, then the number of rows in the cursor may change after the DBMS opens it. As such, the cursor's row count is set to -1 because the server never knows for sure that it has retrieved all qualified rows into the cursor. 0 Either no cursors have been opened yet, the cursor's SELECT statement returned no rows, or the last cursor opened on the connection has since been closed or deallocated. After the DBMS fully populates a cursor, the value of @@CURSOR_ROWS will be the number of rows in the cursor. Chapter 17 Understanding Triggers