System stored procedures

轉自:http://jtds.sourceforge.net/apiCursors.html#_sp_cursoropen

System stored procedures

This document contains information on undocumented stored procedures in Microsoft SQL Server.

	Name	Function
	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.

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_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:

'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

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_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).

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 =] 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 =] 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 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).

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 =] rownum OUTPUT

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).

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_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 =] 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_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).

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_handle OUTPUT

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_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.

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_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).

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 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).

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 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).

Result Sets

Returns a result set if specified by the prepared statement.

Permissions

Execute permissions default to the public role.

Example