OPEN (T-SQL)

Opens a Transact-SQL server cursor and populates the cursor by executing the Transact-SQL statement specified on the DECLARE CURSOR or SET cursor_variable statement.

Syntax

OPEN { { [GLOBAL] cursor_name } | cursor_variable_name}

Arguments

GLOBAL

Specifies that cursor_name refers to a global cursor.

cursor_name

Is the name of a declared cursor. If both a global and a local cursor exist with cursor_name as their name, cursor_name refers to the global cursor if GLOBAL is specified; otherwise, cursor_name refers to the local cursor.

cursor_variable_name

Is the name of a cursor variable that references a cursor.

Remarks

If the cursor is declared with the INSENSITIVE or STATIC option, OPEN creates a temporary table to hold the result set. OPEN fails if the size of any row in the result set exceeds the maximum row size for Microsoft® SQL Server™ tables. If the cursor is declared with the KEYSET option, OPEN creates a temporary table to hold the keyset. The temporary tables are stored in tempdb.

After a cursor has been opened, use the @@CURSOR_ROWS function to receive the number of qualifying rows in the last opened cursor. Depending on the number of rows expected in the result set, SQL Server may choose to populate a keyset-driven cursor asynchronously on a separate thread. This allows fetches to proceed immediately, even if the keyset is not fully populated. For more information, see Asynchronous Population.

To set the threshold at which SQL Server generates keysets asynchronously, set the cursor threshold configuration option. For more information, see sp_configure.

Examples

This example opens a cursor and fetches all the rows.

DECLARE Employee_Cursor CURSOR FOR

SELECT LastName, FirstName

FROM Northwind.dbo.Employees

WHERE LastName like 'B%'

OPEN Employee_Cursor

FETCH NEXT FROM Employee_Cursor

WHILE @@FETCH_STATUS = 0

BEGIN

    FETCH NEXT FROM Employee_Cursor

END

CLOSE Employee_Cursor

DEALLOCATE Employee_Cursor

See Also