Cursor Variables
SQL Server 7 lets you declare variables with a type of cursor. Besides scalar (single-valued) variables, cursor variables are the only type of variable possible. To assign to a cursor variable, you use the SET statement. (Cursor variables cannot be assigned a value with an assignment SELECT, as scalar values can.) Cursor variables can be either the source or the target in SET assignment statements, and they can be used as the type for output parameters. They can be used in any of the cursor management statements: OPEN , FETCH, CLOSE, and DEALLOCATE. However, you cannot have a column in a table of type cursor, and input parameters to stored procedures cannot be cursors .
The following example declares a cursor and then sets a variable to reference the same result set:
DECLARE declared_cursor CURSOR FOR SELECT au_lname FROM authors DECLARE @cursor_var cursor SET @cursor_var = declared_cursor |
The declared cursor and the cursor variable are almost synonymous. You can use either the declared name or the cursor variable name to open the cursorthey are two references to the same internal structure. You can open the cursor with this statement:
OPEN @cursor_var |
Once you do this, the cursor is open. If you then try to open it with the declared name
OPEN declared_cursor |
you get this error:
Msg 16905, Level 16, State 1 The cursor is already open. |
If you issue one FETCH with the declared cursor, a subsequent FETCH from the cursor variable retrieves the next row. A CLOSE using either reference closes the cursor, and no more rows can be fetched . The only time the two references are treated differently is in the DEALLOCATE statement. Deallocating one of the references means only that you can no longer access the cursor using that reference. The internal data structures are not freed until the last reference is deallocated. If the cursor has not yet been closed, only the deallocation of the last reference closes it.
You can have more than one cursor variable referencing the same cursor, as the following example shows:
DECLARE declared_cursor CURSOR LOCAL FOR SELECT title_id, price from titles DECLARE @cursor_var_a CURSOR DECLARE @cursor_var_b CURSOR SET @cursor_var_a = declared_cursor SET @cursor_var_b = @cursor_var_a OPEN @cursor_var_a -- opens declared cursor FETCH @cursor_var_b -- fetches row 1 from declared_cursor FETCH declared_cursor -- fetches row 2 DEALLOCATE declared_cursor -- keeps cursor open since other references remain, -- but can't use declared_cursor name to reference the cursor |
One main use of cursor variables is for output parameters. There are four stored procedures for retrieving information about cursors, and each returns its information in a cursor. We'll look at these procedures in the next section.
Obtaining Cursor Information
Along with the wealth of cursor functionality in SQL Server 7, you have access to a function and several procedures that provide you with information about your cursors. However, these procedures are not always easy to use, so we have provided some additional procedures that go beyond what is offered with the installed SQL Server product.
CURSOR_STATUS
The CURSOR_STATUS() function determines the state of a particular cursor or cursor variable. Five possible values can be returned from this function, and the meaning of the values is slightly different depending on whether the argument is a cursor name or a cursor variable. The function takes two arguments; the first one indicates a global cursor, a local cursor, or a cursor variable (which is always local). The syntax appears at the top of the next page.
CURSOR_STATUS ( { ' local ', ' cursor_name ' } { ' global ', ' cursor_name ' } { ' variable ', ' cursor_variable ' } ) |
Table 11-3 shows the five possible return values and their meanings.
Table 11-3. Meanings of return values from the CURSOR_STATUS() function.
| Return Value | Cursor Name | Cursor Variable |
|---|---|---|
| 1 | The cursor is open, and for static and keyset cursors the result set has at least one row; for dynamic cursors, the result set can have zero, one, or more rows. | The cursor allocated to this variable is open, and for static and keyset cursors the result set has at least one row; for dynamic cursors, the result set can have zero, one, or more rows. |
| The result set of the cursor is empty. (Dynamic cursors never return this result.) | The cursor allocated to this variable is open, but the result set is empty. (Dynamic cursors never return this result.) | |
| -1 | The cursor is closed. | The cursor allocated to this variable is closed. |
| -2 | Not applicable . | No cursor was assigned to this OUTPUT variable by the previously called procedure. OR A cursor was assigned by the previously called procedure, but it was in a closed state when the procedure completed. Therefore, the cursor is deallocated and not returned to the calling procedure. OR There is no cursor assigned to a declared cursor variable. |
| -3 | A cursor with the specified name does not exist. | A cursor variable with the specified name does not exist. OR If one exists it has not yet had a cursor allocated to it. |
We'll use the CURSOR_STATUS() function below when we check the output parameter from the sp_describe_cursor stored procedure.
sp_describe_cursor
This procedure provides information on one cursor or cursor variable whose name must be passed to the procedure. Even though only one row of information is returned, you must supply the procedure with an output parameter of type cursor . After executing the procedure, you must use the cursor FETCH command to retrieve the information from the returned cursor. Here's the syntax for calling the stored procedure:
sp_describe_cursor [@cursor_return =] output_cursor_variable OUTPUT { [, [@cursor_source =] N'local', [@cursor_identity =] N' local_cursor_name '] [, [@cursor_source =] N'global', [@cursor_identity =] N' global_cursor_name '] [, [@cursor_source =] N'variable', [@cursor_identity =] N' input_cursor_variable '] } |
Here's an example of using the procedure and then accessing the data returned in the cursor variable. Authors_cursor is the cursor for which we want information and @Report is a cursor variable to hold the results.
DECLARE authors_cursor CURSOR KEYSET FOR SELECT au_lname FROM authors WHERE au_lname LIKE 'S%' OPEN authors_cursor -- Declare a cursor variable to hold the cursor output variable -- from sp_describe_cursor DECLARE @Report CURSOR -- Execute sp_describe_cursor into the cursor variable EXEC master.dbo.sp_describe_cursor @cursor_return = @Report OUTPUT, @cursor_source = 'global', @cursor_identity = 'authors_cursor' -- Verify that the cursor variable contains information IF cursor_status('variable', '@Report') != 1 print 'No information available from the cursor' ELSE BEGIN -- Fetch all the rows from the sp_describe_cursor output cursor WHILE (@@fetch_status = 0) BEGIN FETCH NEXT from @Report END END -- Close and deallocate the cursor from sp_describe_cursor IF cursor_status('variable','@Report') >= -1 BEGIN CLOSE @Report DEALLOCATE @Report END GO -- Close and deallocate the original cursor CLOSE authors_cursor DEALLOCATE authors_cursor GO |
Here's some of the information returned:
reference_name cursor_scope status model concurrency scrollable open_status -------------- ------------ ------ ----- ----------- ---------- ----------- authors_cursor 2 0 2 3 1 1 cursor_rows fetch_status column_count row_count last_operation ----------- ------------ ------------ --------- -------------- 0 0 1 0 1 |
Table 11-4 shows some of the return values.
Table 11-4. Meaning of values returned by sp_describe_cursor.
| Function | Return Values |
|---|---|
| CURSOR_SCOPE | 1 = LOCAL 2 = GLOBAL |
| STATUS | The same values as reported by the CURSOR_STATUS() system function, described above. |
| MODEL | 1 = Static 2 = Keyset 3 = Dynamic 4 = Fast forward |
| CONCURRENCY | 1 = Read-only 2 = Scroll locks 3 = Optimistic |
| SCROLLABLE | 0 = Forward-only 1 = Scrollable |
| OPEN_STATUS | 0 = Closed 1 = Open |
| CURSOR_ROWS | Number of qualifying rows in the result set. |
| FETCH_STATUS | The status of the last fetch on this cursor, as indicated by the @@FETCH_STATUS function. 0 = The fetch was successful. -1 = The fetch failed or is beyond the bounds of the cursor. -2 = The requested row is missing. -9 = There was no fetch on the cursor. |
| COLUMN_COUNT | The number of columns in the cursor result set. |
| ROW_COUNT | The number of rows affected by the last operation on the cursor, as indicated by the @@ROWCOUNT function. |
| LAST_OPERATION | The last operation performed on the cursor. 0 = No operations have been performed on the cursor. 1 = OPEN 2 = FETCH 3 = INSERT 4 = UPDATE 5 = DELETE 6 = CLOSE 7 = DEALLOCATE |
sp_cursor_list
This procedure returns the same information as sp_describe_cursor , but it does so for all cursors. You can specify whether you want all global cursors, all local cursors, or both. Here's the syntax:
sp_cursor_list [@cursor_return =] cursor_variable_name OUTPUT, [@cursor_scope =] cursor_scope |
You can actually create your own stored procedure as a wrapper around sp_cursor_list. This allows you to avoid the declaration of the @Report cursor output parameter every time you want to look at cursor properties. You also do not have to code the repeated fetching of rows using this cursor variable. Here's an example of what such a procedure might look like. It uses a default of 3 for the value of scope , which means all cursors, but you can override that when you call this new sp_cursor _ info procedure, and you can pass a 1 to indicate local cursors only or a 2 to indicate global cursors only:
use master go create proc sp_cursor_info (@scope int = 3) as -- Declare a cursor variable to hold the cursor output variable -- from sp_cursor_list. DECLARE @Report CURSOR -- Execute sp_cursor_list into the cursor variable. EXEC master.dbo.sp_cursor_list @cursor_return = @Report OUTPUT, @cursor_scope = @scope -- Fetch all the rows from the sp_cursor_list output cursor. WHILE (@@FETCH_STATUS = 0) BEGIN FETCH NEXT from @Report END -- Close and deallocate the cursor from sp_cursor_list. CLOSE @Report DEALLOCATE @Report GO |
The procedure returns individual cursor rows, one for each cursor available in the session. You can expand this procedure even further to populate a temporary table with the values in the returned row. You can then use the CASE functionality to translate the integer code numbers returned into meaningful strings. The companion CD includes a script to build such a procedure, called sp_cursor_ details , which calls sp_cursor_list and receives the results into a cursor variable. By creating the sp_cursor_list and sp_cursor_details procedures in your master database, you can execute sp_cursor_details just like any other system-supplied stored procedure. It will always return at least one row of information because internally it uses a cursor called @Report to process the results of sp_describe_ cursor . If you haven't created any cursors of your own, sp_cursor_ details will return output similar to the output below. (This output has been split into three sets of columns so it all fits on the page of the book, but the output in SQL Server Query Analyzer would be all on one line.
Cursor Reference Declared Name Scope Status ----------------- -------------------------- -------- ------------- @Report _MICROSOFT_SS_0301113436 local closed Model LOCKING Scrolling Open Qualifying Rows Fetch Status ------------ ---------- ------------ ---- --------------- ------------- dynamic read-only scrollable no 0 no fetch done Columns Rows for last operation Last operation Handle ------- ----------------------- -------------- ----------- 14 0 OPEN 301113436 |
sp_describe_cursor_tables
This procedure reports on the tables accessed by a given cursor. It returns its output in a cursor variable, with one row for each table in the specified cursor. It returns such information as the name of the table and any optimizer hints specified for the table. See the SQL Server online documentation for more details.
sp_describe_cursor_columns
This procedure reports the attributes of the columns in the result set of a given cursor. It returns its output in a cursor variable, with one row for each column in the SELECT list of the specified cursor. The information it returns includes the name of the column, the datatype, the ordinal position of the column, and the name of the table from which the cursor is selected. See the SQL Server online documentation for more details.
EAN: 2147483647
Pages: 144