Transact-SQL cursor)

SQL Server supports three types of cursors:
Client cursor
API server cursor
T-SQL cursor

The main difference between T-SQL cursors and other types of cursors is that they are used differently.
The T-SQL used in a stored procedure, batch, function, or trigger is used to repeat each row of cursor for custom processing.
Other types of cursors are used for application from the client.ProgramAccess database information.

Search for "Transact-SQL cursor" in MSSQL online help to obtain detailed information.
Msdn help address for: http://msdn2.microsoft.com/zh-cn/library/ms190028.aspx
Transact-SQL cursor

Transact-SQL cursors are mainly used in Stored Procedures, triggers, and transact-SQL scripts. They make the content of the result set equally available to other Transact-SQL statements.

A typical process of using a Transact-SQL cursor in a stored procedure or trigger is:

1. The declared Transact-SQL variable contains the data returned by the cursor. Declare a variable for each result set column. Declare a variable that is large enough to save the value returned by the column, and declare the data type that can be converted from the column data type in implicit mode.

2. Use the declare cursor statement to associate a Transact-SQL cursor with a select statement. The declare cursor statement defines the features of the cursor at the same time, such as the name of the cursor and whether the cursor is read-only or only-in.

3. Use the open statement to execute the SELECT statement and generate a cursor.

4. Use the fetch into statement to extract a single row and transfer the data in each column to the specified variable. Other Transact-SQL statements can then reference these variables to access extracted data values. Transact-SQL does not support extracting row blocks.

5. When the cursor ends, use the close statement. Closing a cursor can release some resources, such as the cursor result set and locking the current row. However, if an open statement is issued again, the cursor structure can still be used for processing. Because the cursor still exists, you cannot use the cursor name again. The deallocate statement completely releases the resources allocated to the cursor, including the cursor name. After the cursor is released, you must use the declare statement to regenerate the cursor.

Monitor the activity of a Transact-SQL cursor

AvailableSp_cursor_listSystem stored procedures to obtain a list of visible cursors for the current connection.Sp_describe_cursor,Sp_describe_cursor_columnsAndSp_describe_cursor_tablesTo determine the features of the cursor.

After the cursor is opened, the @ cursor_rows function orSp_cursor_listOrSp_describe_cursorReturnedCursor_rowsColumn indicates the number of rows in the cursor.

After each fetch statement is executed, @ fetch_status is updated to reflect the last extracted state. You can alsoSp_describe_cursorReturnedFetch_statusColumn to obtain the status information. @ Fetch_status: report the status in the cursor, for example, exceeding the extraction of the first row and the last row. @ Fetch_status is global for the connection, and is extracted and reset every time the connection cursor is opened. If you need to know the status later, you need to save @ fetch_status in a user variable before executing another statement in the connection. Even if the next statement is not fetch, it may be insert, update, or delete. They can trigger a trigger containing the fetch statement that can be reset @ fetch_status.Sp_describe_cursorReturnedFetch_statusThe column is determined for the specified cursor and is not affected by the fetch statements that reference other cursors.Sp_describe_cursorIt is affected by the fetch statement that references the same cursor. Therefore, you must pay attention to it when using it.

After fetch is completed, the cursor is located on the extracted row. The extracted row is called the current row. If the cursor is not declared as a read-only cursor, You can execute a where currentCursor_nameTo modify the current row.

The name assigned by the declare cursor statement to the transact-SQL cursor can be global or local. The global cursor name can be referenced by any batch processing, stored procedure, or trigger located on the same connection. The local cursor name cannot be referenced outside the batch processing, stored procedure, or trigger that declares the cursor. The partial cursor in the trigger and stored procedure can avoid unintentional references from outside the stored procedure or trigger.

Use cursor variable

Microsoft SQL Server 2000 also supportsCursorData type variable. Two methods can be used for a cursor:CursorVariables are associated:

 
/* Use declare @ local_variable, declare cursor and set .*/
Declare @ myvariable cursor

Declare mycursor cursor
Select lastname from northwind. DBO. Employees

Set @ myvariable = mycursor

/* Use declare @ local_variable and Set */
Declare @ myvariable cursor

Set @ myvariable = cursor scroll keyset
Select lastname from northwind. DBO. Employees
 

When the cursor andCursorAfter the variables are associated, you can useCursorThe variable replaces the cursor name. The stored procedure output parameter can also be assignedCursorData Type and associated with a cursor. This allows the stored procedure to display its local cursor in a controlled manner.

Reference A Transact-SQL cursor

The name and variable of a Transact-SQL cursor can only be referenced by a Transact-SQL statement, but cannot be referenced by APIs of OLE DB, ODBC, ADO, and DB-library. For example, if you want to use declare cursor and open statements to generate a Transact-SQL cursor, you cannot useSqlfetchOrSqlfetchscrollFunction to extract rows from the transact-SQL cursor. Applications that require cursor processing and use these APIs should use the built-in cursor support in the database API to replace the transact-SQL cursor.

By using fetch and binding each column returned by fetch to program variables, you can use a Transact-SQL cursor in the application. Transact-SQL fetch does not support batch processing. Therefore, this is the most efficient way to return data to the application. Each row to be extracted must be sent to and from the server once. The cursor function built in the database API is more effective and supports multi-row extraction.

When included in Stored Procedures and triggers, the transact-SQL cursor is extremely effective. This is because all operations are compiled into an execution plan on the server and there is no network traffic related to row extraction.