Export (0) Print
Expand All

sp_cursoroption (Transact-SQL)

Sets cursor options or returns cursor information created by the sp_cursoropen stored procedure. sp_cursoroption is invoked by specifying ID =8 in a tabular data stream (TDS) packet.

Applies to: SQL Server (SQL Server 2008 through current version).

Topic link icon Transact-SQL Syntax Conventions

sp_cursoroption cursor, code, value

cursor

Is a handle value that is generated by SQL Server and returned by the sp_cursoropen stored procedure. cursor requires an int input value for execution. 

code

Used to stipulate various factors of the cursor return values. code requires one of the following int input values:

Value

Name

Description

0x0001

TEXTPTR_ONLY

Returns the text pointer, and not the actual data, for certain designated text or image columns.

TEXTPTR_ONLY allows text pointers to be used as handles to blob objects which can later be selectively retrieved or updated using Transact-SQL or DBLIB facilities (e.g. Transact-SQL READTEXT or DBLIB DBWRITETEXT).

If a "0" value is assigned, all of the text and image columns in the select list will return text pointers rather than data.

0x0002

CURSOR_NAME

Assigns the name specified in value to the cursor. This, in turn, allows ODBC to use Transact-SQL positioned UPDATE/DELETE statements on cursors opened via sp_cursoropen.

The string can be specified as any character or Unicode data type.

Since Transact-SQL positioned UPDATE/DELETE statements operate, by default, on the first row in a fat cursor, sp_cursor SETPOSITION should be used to position the cursor before issuing the positioned UPDATE/DELETE statement.

0x0003

TEXTDATA

Returns the actual data, not the text pointer, for certain text or image columns on subsequent fetches (i.e. this undoes the effect of TEXTPTR_ONLY).

If TEXTDATA is enabled for a particular column the row is re-fetched or refreshed, and can then be set back to TEXTPTR_ONLY. As with TEXTPTR_ONLY, the value parameter is an integer that specifies the column number and a zero value returns all text or image columns.

0x0004

SCROLLOPT

Scroll option. See "Returned Code Values" later in this topic for additional information.

0x0005

CCOPT

Concurrency control option. See "Returned Code Values" later in this topic for additional information.

0x0006

ROWCOUNT

The number of rows currently in the result set.

Note Note

The ROWCOUNT may have changed since the value returned by sp_cursoropen if asynchronous population is being used. The value –1 is returned if the number of rows is unknown.

value

Designates the value returned by code. value is a required parameter that calls for a 0x0001, 0x0002, or 0x0003 code input value.

Note Note

A code value of 2 is a string data type. Any other code value input or returned by value is an integer.

The value parameter may return one of the following code values.

Return value

Description

0x0004

SCROLLOPT

0X0005

CCOPT

0X0006

ROWCOUNT

The value parameter returns one of the following SCROLLOPT values.

Return value

Description

0x0001

KEYSET

0x0002

DYNAMIC

0x0004

FORWARD_ONLY

0x0008

STATIC

The value parameter returns one of the following CCOPT values.

Return value

Description

0x0001

READ_ONLY

0x0002

SCROLL_LOCKS

0x0004 or 0x0008

OPTIMISTIC

Was this page helpful?
(1500 characters remaining)
Thank you for your feedback

Community Additions

ADD
Show:
© 2014 Microsoft