IDC资讯中心 引用自 http://jtds.sourceforge.net/apiCursors.html System stored procedures Name Function Syntax [ ] Arguments 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_options OUTPUT Value Description On return, @scrollopt contains the type of cursor actually created, which may not match what was requested. Value Description On return, @ccopt contains the type of cursor actually created, which may not match what was requested. Result Sets Remarks Permissions Examples USE pubs — Create a dynamc read-only cursor DECLARE @cursor INT EXEC sp_cursoropen @cursor OUTPUT, NSELECT * FROM myTable, 2, 8193 — Close the cursor EXEC sp_cursorclose @cursor B. Create a cursor for a parameterized SELECT statement USE pubs — Create a dynamc read-only cursor DECLARE @cursor INT EXEC sp_cursoropen @cursor OUTPUT, NSELECT * 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 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 Syntax Arguments Value Description [@rownum =] rownum OUTPUT Result Sets Remarks Permissions Example — 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 Syntax Arguments Result Sets Permissions Example — 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 Syntax Arguments Value Description [@value =] value Result Sets Permissions Example — 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, Nglobal, myCursor FETCH NEXT FROM @x — Use the cursor directly by name FETCH NEXT FROM myCursor — Close the cursor EXEC sp_cursorclose @cursor sp_cursor Syntax Arguments Value Description [@rownum =] rownum Result Sets Permissions Example — 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 Syntax 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 Result Sets Permissions Example Syntax Arguments 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_options OUTPUT Result Sets Permissions Example Syntax Arguments Result Sets Permissions Example Syntax Arguments Result Sets Permissions Example Syntax 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 Result Sets Permissions Example Syntax Arguments Result Sets Permissions Example Syntax Arguments Result Sets Permissions Example Syntax Arguments 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 Result Sets Permissions Example
This document contains information on undocumented stored procedures in Microsoft SQL Server.
sp_cursor Update a cursor
sp_cursorclose close a cursor
sp_cursorexecute Open a prepared cursor
sp_cursorfetch Fetch rows
sp_cursoropen Open a cursor
sp_cursoroption Set cursor options
sp_cursorprepare Prepare a cursor statement
sp_cursorprepexec Prepare a cursor statement and open
sp_cursorunprepare Free a prepared cursor statement
sp_execute Execute a prepared statement
sp_prepare Prepare an SQL statement
sp_prepexec Prepare and execute an SQL statement
sp_unprepare Free a prepared statement
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.
sp_cursoropen [@cursor =] cursor_handle OUTPUT,
[@stmt =] stmt
[, [@scrollopt =] scroll_options OUTPUT]
[, [@ccopt =] concurrency_options OUTPUT]
[, [@rowcount =] rowcount OUTPUT]
{, [@paramdef =] Nparameter_name data_type [,…n] }
{, [@param1 =] value1 [,…n] }
[@cursor =] cursor_handle OUTPUT
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:
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).
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.
[@ccopt =] concurrency_options OUTPUT
Is the cursor concurrency. concurrency_options is int, with a default of 4 (optimistic) and can be a combination of these values (exactly one of the first 4 must be specified).
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.
[@rowcount =] rowcount OUTPUT
Is the name of a declared integer variable to receive the number of affected rows. rowcount is int with no default value.
[@paramdef =] Nparameter_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 stmt has 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).
Returns the result set generated by stmt, but containing no rows.
sp_cursoropen is a more powerful (and programmatic) way of creating server-side cursors on SQL Server.
Execute permissions default to the public role.
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.
This example creates a dynamic read-only cursor for a SELECT statement with 2 parameters.
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.
Fetches a row or block of rows from an API server cursor.
sp_cursorfetch [@cursor =] cursor_handle
[, [@fetchtype =] fetchtype]
[, [@rownum =] rownum OUTPUT]
[, [@nrows =] nrows OUTPUT]
[@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.
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 (???).
Is the row number. rownum is int, with a default of NULL.
[@nrows =] nrows OUTPUT
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).
Returns the requested row or group of rows from the cursor.
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).
Execute permissions default to the public role.
USE pubs
Closes ande deallocates an API server cursor.
sp_cursorclose [@cursor =] cursor_handle
[@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).
None.
Execute permissions default to the public role.
USE pubs
Sets various options for API server cursors.
sp_cursoroption [@cursor =] cursor_handle,
[@code =] code,
{ [@value =] value
| [@cursorname =] cursorname }
[@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.
1 Only return the TEXTPTR of the LOB column specified by value
2 Set cursor name.
Is the value of the selected option (for option 1 its 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).
None.
Execute permissions default to the public role.
USE pubs
Can be used to request inserts and positioned updates or deletes on API server cursors.
sp_cursor [@cursor =] cursor_handle,
[@optype =] optype,
[@rownum =] rownum,
[@table =] table
{, [@param1 =] value1 [,…n] }
[@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.
1 Update row (?).
4 Insert row.
33 Update row.
34 Delete row.
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).
None.
Execute permissions default to the public role.
USE pubs
Used to prepare a parameterized cursor statement.
sp_cursorprepare [@cursor =] statement_handle OUTPUT,
[@paramdef =] Nparameter_name data_type [,…n],
[@stmt =] Nstmt,
[@options =] options,
[, [@scrollopt =] scroll_options OUTPUT]
[, [@ccopt =] concurrency_options OUTPUT]
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 =] Nparameter_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:
An integer value. The exact function of this parameter is unknown. options is int, with a value of 1.
[@scrollopt =] scroll_options OUTPUT
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_options OUTPUT
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).
None.
Execute permissions default to the public role.
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.
sp_cursorprepexec [@handle =] statement_handle OUTPUT,
[@cursor =] cursor_handle OUTPUT,
[@paramdef =] Nparameter_name data_type, [,…n]
[@stmt =] Nstmt,
[, [@scrollopt =] scroll_options OUTPUT]
[, [@ccopt =] concurrency_options OUTPUT]
[, [@rowcount =] rowcount OUTPUT]
[@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_handle OUTPUT
Is the name of a declared integer variable to receive the cursor handle. cursor_handle is int, with no default.
[@paramdef =] Nparameter_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:
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_options OUTPUT
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 =] rowcount OUTPUT
Is the name of a declared integer variable to receive the number of affected rows. rowcount is int with no default value.
Return Code Values
0 (success) or 1 (failure).
Returns the result set generated by stmt, but containing no rows.
Execute permissions default to the public role.
sp_cursorexecute
Used to execute (open) a prepared cursor statement.
sp_cursorexecute [@handle =] statement_handle,
[@cursor =] cursor_handle OUTPUT,
[, [@scrollopt =] scroll_options OUTPUT]
[, [@ccopt =] concurrency_options OUTPUT]
[, [@rowcount =] rowcount OUTPUT]
{, [@param1 =] value1 [,…n] }
[@handle =] statement_handle
Is the integer value of the statement handle. statement_handle is int, with no default.
[@cursor =] cursor_handle OUTPUT
Is the name of a declared integer variable to receive the cursor handle. cursor_handle is int, with no default.
[@scrollopt =] scroll_options OUTPUT
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_options OUTPUT
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 =] rowcount OUTPUT
Is the name of a declared integer variable to receive the number of affected rows. rowcount is int with 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 stmt has 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).
Returns the result set generated by the prepared statement handle, but containing no rows.
Execute permissions default to the public role.
sp_cursorunprepare
Used to free a prepared cursor statement.
sp_cursorunprepare [@handle =] statement_handle
[@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).
None.
Execute permissions default to the public role.
sp_prepare
Used to prepare a parameterized SQL statement.
sp_prepare [@handle =] statement_handle OUTPUT,
[@paramdef =] Nparameter_name data_type [,…n],
[@stmt =] Nstmt,
[@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 =] Nparameter_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:
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).
None.
Execute permissions default to the public role.
sp_execute
Used to execute a prepared SQL statement.
sp_execute [@handle =] statement_handle
{, [@param1 =] value1 [,…n] }
[@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 stmt has 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).
Returns a result set if specified by the prepared statement.
Execute permissions default to the public role.
sp_unprepare
Used to free a prepared SQL statement.
sp_unprepare [@handle =] statement_handle
[@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).
None.
Execute permissions default to the public role.
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.
sp_prepexec [@handle =] statement_handle OUTPUT,
[@paramdef =] Nparameter_name data_type, [,…n]
[@stmt =] Nstmt,
{, [@param1 =] value1 [,…n] }
[@handle =] statement_handle
Is the name of a declared integer variable to receive the statement handle. statement_handle is int, with no default.
[@paramdef =] Nparameter_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:
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 stmt has 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).
Returns a result set if specified by the prepared statement.
Execute permissions default to the public role.
一些在sql帮助中找不到的系统存储过程－system stored procedures _数据库技巧
版权申明：本站文章部分自网络，如有侵权，请联系：west999com@outlook.com 特别注意：本站所有转载文章言论不代表本站观点！ 本站所提供的图片等素材，版权归原作者所有，如需使用，请与原作者联系。未经允许不得转载：IDC资讯中心 » 一些在sql帮助中找不到的系统存储过程－system stored procedures _数据库技巧
