SQL Server API cursor was designed for data access components years ago. It’s sharing the same framework of T-SQL cursors with more powerful features, such as create cursor over a return set from a procedure. This article lists procedures for API cursor. They are undocumented and deprecated. Please use them in caution. The major part of this article is copied it from http://jtds.sourceforge.net/apiCursors.html.
Stored Procedures for API Cursor
This document contains information on undocumented stored procedures in Microsoft SQL Server.
sp_cursoropen
Defines the attributes of an API server cursor, such as its scrolling behavior and the statement used to build the result set on which the cursor operates, then populates the cursor. The statement can contain embedded parameters.
Syntax
sp_cursoropen [@cursor =] cursor_handle OUTPUT, [@stmt =] ‘stmt‘ [, [@scrollopt =]scroll_options OUTPUT][, [@ccopt =]concurrency_options OUTPUT][, [@rowcount =]rowcount OUTPUT][{, [@paramdef =] N’parameter_name data_type[,…n]’ }{, [@param1 =] value1[,…n] }]
Arguments
- [@cursor =] cursor_handleOUTPUT
- Is the name of a declared integer variable to receive the cursor handle.
cursor_handle is int, with no default.
- [@stmt =] ‘stmt‘
- Is a string containing a single SELECT statement or a single stored procedure call. The size of the string is limited only by available database server memory. stmt can contain parameters having the same form as a variable name, for example:
'SELECT * FROM Employees WHERE EmployeeID = @IDParameter' Each parameter included in stmt must have a corresponding entry in both the @paramdef parameter definition list and the parameter values list.
- [@scrollopt =] scroll_optionsOUTPUT
- Is the cursor scroll type. scroll_options is int with a
default of 1 (keyset-driven), and can be a combination of these values (exactly one of the first 5 must be specified).
| Value |
Description |
| 0x0001 |
Keyset-driven cursor. |
| 0x0002 |
Dynamic cursor. |
| 0x0004 |
Forward-only cursor. |
| 0x0008 |
Static cursor. |
| 0x0010 |
Fast forward-only cursor. |
| 0x1000 |
Parameterized query. |
| 0x2000 |
Auto fetch. |
| 0x4000 |
Auto close. |
| 0x8000 |
Check acceptable types. |
| 0x10000 |
Keyset-driven acceptable. |
| 0x20000 |
Dynamic acceptable. |
| 0x40000 |
Forward-only acceptable. |
| 0x80000 |
Static acceptable. |
| 0x100000 |
Fast forward-only acceptable. |
On return,
@scrollopt contains the type of cursor actually created, which may not match what was requested.
- [@ccopt =] concurrency_optionsOUTPUT
- Is the cursor concurrency. concurrency_options is strong>int, with a default of 4 (optimistic) and can be a combination of these values (exactly one of the first 4 must be specified).
| Value |
Description |
| 0x0001 |
Read-only. |
| 0x0002 |
Scroll locks. |
| 0x0004 |
Optimistic. Checks timestamps and, when not available, values. |
| 0x0008 |
Optimistic. Checks values (non-text, non-image). |
| 0x2000 |
Open on any SQL. |
| 0x4000 |
Update keyset in place. |
| 0x10000 |
Read-only acceptable. |
| 0x20000 |
Locks acceptable. |
| 0x40000 |
Optimistic acceptable. |
On return, @ccopt contains the type of cursor actually created, which may not match what was requested.
- [@rowcount =] rowcountOUTPUT
- Is the name of a declared integer variable to receive the number of affected rows. rowcount is intwith no default value.
- [@paramdef =] N’parameter_name data_type, …n]’
- Is one string that contains the definitions of all parameters that have been embedded in stmt. Each parameter definition consists of a parameter name and a data type. n is a placeholder indicating additional parameter definitions. Every parameter specified in stmt must be defined in @paramdef. If the Transact-SQL statement in stmt does not contain parameters, @paramdef is not needed. The default value for this parameter is NULL.
- [@param1 =] value1
- Is a value for the first parameter defined in the parameter string. The value can be a constant or a variable. There must be a parameter value supplied for every parameter included in stmt. The values are not needed if the Transact-SQL statement in stmthas no parameters.
- n
- Is a placeholder for the values of additional parameters. Values can be only constants or variables. Values cannot be more complex expressions such as functions, or expressions built using operators.
Return Code Values
0 (success) or 1 (failure).
Result Sets
Returns the result set generated by stmt, but containing no rows.
Remarks
sp_cursoropen is a more powerful (and programmatic) way of creating server-side cursors on SQL Server.
Permissions
Execute permissions default to the public role.
Examples
A. Create a cursor for a simple SELECT statement
This simple example creates a dynamic read-only cursor for a SELECT statement with no parameters.
USE pubs
-- Create a dynamc read-only cursor
DECLARE @cursor INT
EXEC sp_cursoropen @cursor OUTPUT, N'SELECT * FROM myTable', 2, 8193
-- Close the cursor
EXEC sp_cursorclose @cursor
B. Create a cursor for a parameterized SELECT statement
This example creates a dynamic read-only cursor for a SELECT statement with 2 parameters.
USE pubs
-- Create a dynamc read-only cursor
DECLARE @cursor INT
EXEC sp_cursoropen @cursor OUTPUT, N'SELECT * FROM myTable WHERE col1=@P1 AND col2 LIKE @P2', 2, 8193, N'@P1 INT, @P2 VARCHAR(255)', 10, '%x%'
-- Close the cursor
EXEC sp_cursorclose @cursor
C. Create a cursor for a stored procedure call
This example creates a dynamic read-only cursor for a stored procedure with 2 parameters (the procedure must return only one result set or the cursor creation
will fail). Note that output parameters can also be used and return values retrieved via output parameters.
USE pubs
-- Create a dynamc read-only cursor
DECLARE @cursor INT
DECLARE @retval INT
EXEC sp_cursoropen @cursor OUTPUT, 'EXEC @P1=myProc @P2', 2, 8193, N'@P1 INT OUTPUT, @P2 INT', @retval, 1
-- Close the cursor
EXEC sp_cursorclose @cursor
sp_cursorfetch
Fetches a row or block of rows from an API server cursor.
Syntax
sp_cursorfetch [@cursor =] cursor_handle[,[@fetchtype =] fetchtype][, [@rownum =]rownum OUTPUT][, [@nrows =] nrows OUTPUT]
Arguments
- [@cursor =] cursor_handle
- Is the cursor handle. cursor_handle is int, with no default.
- [@fetchtype =] fetchtype
- Is the fetch type. fetchtype is int, with a default of 2 and can have one of these values.
| Value |
Description |
| 0x0001 |
First row. |
| 0x0002 |
Next row. |
| 0x0004 |
Previous row. |
| 0x0008 |
Last row. |
| 0x0010 |
Absolute row index. |
| 0x0020 |
Relative row index. |
| 0x0040 |
By value (???). |
| 0x0080 |
Refresh. |
| 0x0100 |
Result set info. |
| 0x0200 |
Previous noadjust (?). |
| 0x0400 |
Skip update concurrency (???). |
- [@rownum =] rownumOUTPUT
- Is the row number. rownum is int, with a default of NULL.
- [@nrows =] nrowsOUTPUT
- Is the number of rows to fetch. nrows is int, with a default of NULL (fetch all rows).
Return Code Values
0 (success) or 1 (failure).
Result Sets
Returns the requested row or group of rows from the cursor.
Remarks
In addition to fetching rows, the ‘result set info’ fetch type can be used to retrieve information about the cursor (current row in @rownum and total number of rows in @nrows).
Permissions
Execute permissions default to the public role.
Example
USE pubs
-- Create a dynamc read-only cursor
DECLARE @cursor INT
EXEC sp_cursoropen @cursor OUTPUT, 'SELECT * FROM myTable', 2, 8193
-- Fetch the next 3 lines
EXEC sp_cursorfetch @cursor, 2, 0, 3
-- Close the cursor
EXEC sp_cursorclose @cursor
sp_cursorclose
Closes ande deallocates an API server cursor.
Syntax
sp_cursorclose [@cursor =] cursor_handle
Arguments
- [@cursor =] cursor_handle
- Is a cursor handle obtained by calling sp_cursorcreate. cursor_handle is int, with no default.
Return Code Values
0 (success) or 1 (failure).
Result Sets
None.
Permissions
Execute permissions default to the public role.
Example
USE pubs
-- Create a dynamic read-only cursor
DECLARE @cursor INT
EXEC sp_cursoropen @cursor OUTPUT, 'SELECT * FROM myTable', 2, 8193
-- Close the cursor
EXEC sp_cursorclose @cursor
sp_cursoroption
Sets various options for API server cursors.
Syntax
sp_cursoroption [@cursor =] cursor_handle,[@code =] code,{ [@value=] value | [@cursorname =] cursorname }
Arguments
- [@cursor =] cursor_handle
- Is a cursor handle obtained by calling sp_cursorcreate. cursor_handle is int, with no default.
- [@code =] code
- Is the option code. code is int, with no default and can be one of these values.
| Value |
Description |
| 1 |
Only return the TEXTPTR of the LOB column specified by value |
| 2 |
Set cursor name. |
- [@value =] value
- Is the value of the selected option (for option 1 it’s the index of the LOB column). value is int, with no default.
- [@cursorname =] cursorname
- Is the name for the cursor. cursorname is sysname, with no default.
Return Code Values
0 (success) or 1 (failure).
Result Sets
None.
Permissions
Execute permissions default to the public role.
Example
USE pubs
-- Create a dynamc cursor
DECLARE @cursor INT
EXEC sp_cursoropen @cursor OUTPUT, 'SELECT * FROM myTable', 2, 8193
-- Name the cursor
EXEC sp_cursoroption @cursor, 2, 'myCursor'
-- Use a cursor variable to access the cursor
DECLARE @x CURSOR
EXEC sp_describe_cursor @x out, N'global','myCursor'
FETCH NEXT FROM @x
-- Use the cursor directly by name
FETCH NEXT FROM myCursor
-- Close the cursor
EXEC sp_cursorclose @cursor
sp_cursor
Can be used to request inserts and positioned updates or deletes on API server cursors.
Syntax
sp_cursor [@cursor =] cursor_handle, [@optype=] optype,[@rownum =] rownum,[@table=] ‘table‘{, [@param1 =] value1 [,…n] }
Arguments
- [@cursor =] cursor_handle
- Is a cursor handle obtained by calling sp_cursorcreate. cursor_handle is int, with no default.
- [@optype =] optype
- Is a the operation to perform. optype is int, with no default and can be one of these values.
| Value |
Description |
| 1 |
Update row (?). |
| 4 |
Insert row. |
| 33 |
Update row. |
| 34 |
Delete row. |
- [@rownum =] rownum
- Is the number of the row to update in the fetch cache. rownum is int, with no default.
- [@table =] ‘table‘
- Is the name of the table to update (an empty character string seems to be ok). table is sysname, with no default.
Return Code Values
0 (success) or 1 (failure).
Result Sets
None.
Permissions
Execute permissions default to the public role.
Example
USE pubs
-- Create a dynamc cursor
DECLARE @cursor INT
EXEC sp_cursoropen @cursor OUTPUT, 'SELECT * FROM myTable', 2, 8193
-- Fetch the next 2 lines; this puts lines 1 and 2 in the fetch buffer
EXEC sp_cursorfetch @cursor, 2, 0, 2
-- Update the second line in the fetch buffer
EXEC sp_cursor @cursor, 33, 2, '', @intCol=5, @charCol='x'
-- Close the cursor
EXEC sp_cursorclose @cursor
sp_cursorprepare
Used to prepare a parameterized cursor statement.
Syntax
sp_cursorprepare [@cursor =] statement_handle OUTPUT, [@paramdef =] N’parameter_name data_type[,…n]’,[@stmt =] N’stmt‘,[@options=] options,[, [@scrollopt =] scroll_optionsOUTPUT][, [@ccopt =] concurrency_optionsOUTPUT]
Arguments
- [@cursor =] statement_handle
- Is the name of a declared integer variable to receive the statement handle. statement_handle is int, with no default.
- [@paramdef =] N’parameter_name data_type, …n]’
- Is one string that contains the definitions of all parameters that have been embedded in stmt. Each parameter definition consists of a parameter name and a data type. n is a placeholder indicating additional parameter definitions. Every parameter specified in stmt must be defined in @paramdef.
- [@stmt =] ‘stmt‘
- Is a string containing a single SELECT statement or a single stored procedure call. The size of the string is limited only by available database server memory. stmt can contain parameters having the same form as a variable name, for example:
'SELECT * FROM Employees WHERE EmployeeID = @IDParameter'Each parameter included in stmt must have a corresponding entry in both the @paramdef parameter definition list.
- [@options =] options
- An integer value. The exact function of this parameter is unknown. options is int, with a value of 1.
- [@scrollopt =] scroll_optionsOUTPUT
- Is the cursor scroll type. scroll_options is int with a default of 1 (keyset-driven). See sp_cursoropen for more information. On return, @scrollopt contains the type of cursor actually created, which may not match what was requested.
- [@ccopt =] concurrency_optionsOUTPUT
- Is the cursor concurrency. concurrency_options is int, with a default of 4 (optimistic). See sp_cursoropen for more information. On return, @ccopt contains the type of cursor actually created, which may not match what was requested.
Return Code Values
0 (success) or 1 (failure).
Result Sets
None.
Permissions
Execute permissions default to the public role.
Example
sp_cursorprepexec
Used to prepare and open a parameterized cursor statement. This command combines the functions of the sp_cursorprepare and sp_cursorexecute procedures
and is available from SQL2000 onwards.
Syntax
sp_cursorprepexec [@handle =] statement_handle OUTPUT, [@cursor =] cursor_handle OUTPUT, [@paramdef =] N’parameter_name data_type, [,…n]’ [@stmt =] N’stmt‘, [, [@scrollopt =] scroll_options OUTPUT] [, [@ccopt =] concurrency_options OUTPUT] [, [@rowcount =]
rowcount OUTPUT]
Arguments
- [@handle =] statement_handle
- Is the name of a declared integer variable to receive the statement handle.
statement_handle is int, with no default.
- [@cursor =] cursor_handleOUTPUT
- Is the name of a declared integer variable to receive the cursor handle. cursor_handle is int, with no default.
- [@paramdef =] N’parameter_name data_type [,…n]’
- Is one string that contains the definitions of all parameters that have been embedded in stmt. Each parameter definition consists of a parameter name and a data type. n is a placeholder indicating additional parameter
definitions. Every parameter specified in stmt must be defined in @paramdef.
- [@stmt] ‘stmt‘
- Is a string containing a single SELECT statement or a single stored procedure call. The size of the string is limited only by available database server memory. stmt can contain parameters having the same form as a variable name, for example:
'SELECT * FROM Employees WHERE EmployeeID =@IDParameter'Each parameter included in stmt must have a corresponding entry in both the @paramdef parameter definition list.
- [@scrollopt =] scroll_optionsOUTPUT
- Is the cursor scroll type.scroll_options is int with a default of 1 (keyset-driven). See sp_cursoropen for more information. On return, @scrollopt contains the type of cursor actually created, which may not match what was requested.
- [@ccopt =]concurrency_optionsOUTPUT
- Is the cursor concurrency. concurrency_options is int, with a default of 4 (optimistic). See sp_cursoropen for more information. On return, @ccopt contains the type of cursor actually created, which may not match what was requested.
- [@rowcount =] rowcountOUTPUT
- Is the name of a declared integer variable to receive the number of affectedrows. rowcount is int with no default value.
Return Code Values
0 (success) or 1 (failure).
Result Sets
Returns the result set generated by stmt, but containing no rows.
Permissions
Execute permissions default to the public role.
Example
sp_cursorexecute
Used to execute (open) a prepared cursor statement.
Syntax
sp_cursorexecute [@handle =] statement_handle, [@cursor =] cursor_handle OUTPUT,
[, [@scrollopt =] scroll_options OUTPUT] [, [@ccopt =] concurrency_options OUTPUT] [, [@rowcount =] rowcount OUTPUT] {, [@param1 =] value1
[,…n] }
Arguments
- [@handle =] statement_handle
- Is the integer value of the statement handle. statement_handle is
int, with no default.
- [@cursor =] cursor_handleOUTPUT
- Is the name of a declared integer variable to receive the cursor handle. cursor_handle is int, with no default.
- [@scrollopt =] scroll_optionsOUTPUT
- Is the cursor scroll type. scroll_options is int with a default of 1 (keyset-driven). See sp_cursoropen for more information. On return, @scrollopt contains the type of cursor actually created, which may not match what was requested.
- [@ccopt =] concurrency_optionsOUTPUT
- Is the cursor concurrency. concurrency_options is int, with a default of 4 (optimistic). See sp_cursoropen for more information. On return, @ccopt contains the type of cursor actually created, which may not match what was requested.
- [@rowcount =] rowcountOUTPUT
- Is the name of a declared integer variable to receive the number of affected rows. rowcount is intwith no default value.
- [@param1 =] value1
- Is a value for the first parameter defined in the parameter string. The
value can be a constant or a variable. There must be a parameter value supplied for every parameter included in stmt. The values are not needed if the
Transact-SQL statement in stmthas no parameters.
- n
- Is a placeholder for the values of additional parameters. Values can be only constants or variables. Values cannot be more complex expressions such as functions, or expressions built using operators.
Return Code Values
0 (success) or 1 (failure).
Result Sets
Returns the result set generated by the prepared statement handle, but containing no rows.
Permissions
Execute permissions default to the public role.
Example
sp_cursorunprepare
Used to free a prepared cursor statement.
Syntax
sp_cursorunprepare [@handle =] statement_handle
Arguments
- [@handle =] statement_handle
- Is the integer value of the statement handle. statement_handle is int, with no default.
Return Code Values
0 (success) or 1 (failure).
Result Sets
None.
Permissions
Execute permissions default to the public role.
Example
sp_prepare
Used to prepare a parameterized SQL statement.
Syntax
sp_prepare [@handle =] statement_handle OUTPUT, [@paramdef =] N’parameter_name data_type [,…n]’, [@stmt =] N’stmt‘, [@flag =] flag,
Arguments
- [@handle =] statement_handle
- Is the name of a declared integer variable to receive the statement handle. statement_handle is int, with no default.
- [@paramdef =] N’parameter_name data_type, …n]’
- Is one string that contains the definitions of all parameters that have been embedded in stmt. Each parameter definition consists of a parameter name and a data type. n is a placeholder indicating additional parameter definitions. Every parameter specified in stmt must be defined in @paramdef.
- [@stmt =] ‘stmt‘
- Is a string containing a single SELECT statement or a single stored procedure call. The size of the string is limited only by available database server memory. stmt can contain parameters having the same form as a variable name, for example:
'SELECT * FROM Employees WHERE EmployeeID = @IDParameter'Each parameter included in stmt must have a corresponding entry in both the @paramdef parameter definition list.
- [@flag =] flag
- An integer value. The exact function of this flag is unknown. flag is int, with value of 1.
Return Code Values
0 (success) or 1 (failure).
Result Sets
None.
Permissions
Execute permissions default to the public role.
Example
sp_execute
Used to execute a prepared SQL statement.
Syntax
sp_execute [@handle =] statement_handle{,[@param1 =] value1 [,…n] }
Arguments
- [@handle =] statement_handle
- Is the integer value of the statement handle. statement_handle is
int, with no default.
- [@param1 =] value1
- Is a value for the first parameter defined in the parameter string. The value can be a constant or a variable. There must be a parameter value supplied for every parameter included in prepared statement handle. The values are not needed if the Transact-SQL statement in stmthas no parameters.
- n
- Is a placeholder for the values of additional parameters. Values can be only constants or variables. Values cannot be more complex expressions such as
functions, or expressions built using operators.
Return Code Values
0 (success) or 1 (failure).
Result Sets
Returns a result set if specified by the prepared statement.
Permissions
Execute permissions default to the public role.
Example
sp_unprepare
Used to free a prepared SQL statement.
Syntax
sp_unprepare [@handle =] statement_handle
Arguments
- [@handle =] statement_handle
- Is the integer value of the statement handle. statement_handle is int, with no default.
Return Code Values
0 (success) or 1 (failure).
Result Sets
None.
Permissions
Execute permissions default to the public role.
Example
sp_prepexec
Used to prepare and execute a parameterized SQL statement. This command combines the functions of the sp_prepare and sp_execute procedures and is
available from SQL2000 onwards.
Syntax
sp_prepexec [@handle =] statement_handle OUTPUT, [@paramdef =] N’parameter_name data_type, [,…n]’ [@stmt =] N’stmt‘, {, [@param1 =] value1 [,…n] }
Arguments
- [@handle =] statement_handle
- Is the name of a declared integer variable to receive the statement handle. statement_handle is int, with no default.
- [@paramdef =] N’parameter_name data_type,…n]’
- Is one string that contains the definitions of all parameters that have been embedded in stmt. Each parameter definition consists of a parameter name and a data type. n is a placeholder indicating additional parameter definitions. Every parameter specified in stmt must be defined in @paramdef.
- [@stmt =] ‘stmt‘
- Is a string containing a valid SQL statement. The size of the string is limited only by available database server memory. stmt can contain parameters having the same form as a variable name, for example:
'SELECT * FROM Employees WHERE EmployeeID = @IDParameter'Each parameter included in stmt must have a corresponding entry in both the @paramdef parameter definition list.
- [@param1 =]value1
- Is a value for the first parameter defined in the parameter string. The value can be a constant or a variable. There must be a parameter value supplied for every parameter included in stmt. The values are not needed if the Transact-SQL statement in stmthas no parameters.
- n
- Is a placeholder for the values of additional parameters. Values can be only constants or variables. Values cannot be more complex expressions such as functions, or expressions built using operators.
Return Code Values
0 (success) or 1 (failure).
Result Sets
Returns a result set if specified by the prepared statement.
Permissions
Execute permissions default to the public role.
Example