Three important cursoropen

What are the functions of these three stored procedures ﹖

Sp_cursoropen,

Sp_cursorfetch,

Sp_cursorclose

The API Server cursor implements the SQL Server OLE DB provider,

The SQL Server ODBC driver and DB-Library DLL use these special system stored procedures to indicate cursor operations to the Server.

Sp_cursoropen defines SQL statements related to the cursor and cursor options, and then generates the cursor.

Sp_cursorfetch extracts one or more rows from the cursor.

Sp_cursorclose close and release the cursor.

Sp_cursoroption sets various cursor options.

Sp_cursor is used to request location updates.

Sp_cursorprepare compiles cursor-related statements or batches into execution plans, but does not create a cursor.

Sp_cursorexecute creates and fills in the cursor from the execution plan created by sp_cursorprepare.

Sp_cursorunprepare discards the execution plan generated by sp_cursorprepare.

These system stored procedures are displayed in the SQL Server event probe trace for the ADO, OLE DB, ODBC, and DB-Library applications that use API Server cursors.

These records are only used inside the SQL Server ole db provider, SQL Server ODBC driver, and DB-Library DLL.

Applications can use the complete functions of these processes through the database API cursor function. The procedure specified in an application is not supported.

When SQL Server executes a Statement on a connection, other statements can be executed on the connection only when all the results from the first statement are processed or canceled.

When using the API Server cursor, this rule is still valid, but from the application perspective, it seems that SQL Server has started to support multiple active statements on a connection.

This is because the complete result set is stored in the Server cursor, And the only statement passed to SQL Server is the execution of the storage process of sp_cursor.

SQL Server executes these stored procedures. Once the client retrieves the result set, it can start executing other statements.

The ole db provider and ODBC driver always retrieve all result sets from the sp_cursor stored procedure before returning the control to the application.

This allows the application to be inserted into the multi-level Active Server cursor for extraction.

-- By others Baidu Space