Export (0) Print
Expand All
ABS
ALL
AND
ANY
AVG
bit
COS
COT
DAY
EXP
GO
IN
LEN
LOG
MAX
MIN
NOT
OR
PI
SET
SIN
STR
SUM
TAN
USE
VAR
Expand Minimize

OPEN

SQL Server 2000

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

CLOSE

@@CURSOR_ROWS

DEALLOCATE

DECLARE CURSOR

FETCH

Was this page helpful?
(1500 characters remaining)
Thank you for your feedback
Show:
© 2014 Microsoft