Transaction-sql Cursors

The cursor is based on the DECLARE CURSOR syntax and is used primarily in transaction-sql scripts, stored procedures, and triggers. The Transaction-sql cursor processes the TRANSACTION-SQL statement sent by the client to the server on the server.

The process of using a TRANSACTION-SQL cursor in a stored procedure or trigger is:

(1) declares that the Transaction-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 hold the value returned by the column and declare the type of the variable to be the data type that can be implicitly converted from the data type.

(2) Use the DECLARE CURSOR statement to associate the TRANSACTION-SQL cursor with the SELECT statement. You can also use the DECLARE cursor to define features such as read-only, forward-only, etc. of the cursor.

(3) Use the Open statement to execute the SELECT statement to populate the cursor.

(4) Use the FETCH into statement to extract a single row and move the data in each column to the specified variable. Note: Other Transaction-sql statements can refer to those variables to access the extracted data values. Transaction-sql cursors do not support extracting row blocks.

(5) Use the Close statement to end the use of the cursor. Note: After you close the cursor, the cursor is still present and can be opened using the Open command, and only the call to the DEALLOCATE statement will be released completely.

Client Cursors

The cursor caches the entire result set on the client using the default result set, and all cursor operations are performed in the client's cache. Note: client-side cursors only support forward-only and static cursors. No other cursors are supported.

"Reprint" http://www.cnblogs.com/94cool/archive/2010/04/20/1715951.html

Transaction-sql Cursors