MS SQL Basics Tutorial: Opening cursors

Open cursor Cursor after declaration, if you want to read data from a cursor, you must open the cursor. Open a Transact-SQL Server cursor using the Open command, whose syntax rules are:

OPEN {{[GLOBAL] cursor_name} | cursor_variable_name}

The parameters are described as follows:

Global

Defines a cursor as a global cursor.

cursor_name

is the name of the cursor declared. If a global cursor and a local cursor both use the same cursor name, then the global cursor is indicated by using global, otherwise it is a local cursor.

Cursor_variable_name

is a cursor variable. When a cursor is opened, MS SQL SERVER first checks to see if the syntax for declaring the cursor is correct, and if there are variables in the cursor declaration, the variable values are brought in.

When a cursor is opened, if a insensitive or static reserved word is used in a cursor declaration statement, open produces a temporary table to hold the result set, and the open command fails if any row of data in the result set exceeds the maximum row size defined by MS SQL Server If the cursor is declared with the keyset option, open generates a temporary table to hold the key value. All temporary tables exist in the tempdb database.

After the cursor is successfully opened, the @ @CURSOR_ROWS global variable is used to record the number of rows in the cursor. To improve performance, MS SQL Server allows you to asynchronously read data from the underlying table to a keyset or static cursor, that is, if the query optimizer for MS SQL Server estimates that the row of data returned to the cursor from the underlying table has exceeded sp_configure cursor Threshold parameter value, MS SQL SERVER will start another separate thread to continue reading from the underlying table the row of data that conforms to the cursor definition, from the cursor. To read data for processing without waiting for all data rows that match the cursor definition to be read from the underlying table to the cursor @ @CURSOR_ROWS variable stores the data rows that the cursor has read from the underlying table when the @ @CURSOR_ROWS is invoked. The @ @CURSOR_ROWS return value has the following four, as shown in table 13-1.

If an open cursor is declared with a scroll or insensitive reserved word, the value of @ @CURSOR_ROWS is positive and all rows of data for that cursor. If one of these two reserved words is not added, the value of @ @CURSOR_ROWS is-1, indicating that there is only one data record in the cursor.

See the full set of "MS SQL Basics Tutorials"