open-transact-sql.md

title	description	author	ms.author	ms.date	ms.service	ms.subservice	ms.topic	ms.custom	f1_keywords	helpviewer_keywords	dev_langs	monikerRange
OPEN (Transact-SQL)	OPEN (Transact-SQL)	rwestMSFT	randolphwest	03/14/2017	sql	t-sql	reference	ignite-2024	OPEN_TSQL OPEN	opening cursors cursors [SQL Server], opening populating cursors [SQL Server] OPEN statement Transact-SQL cursors, opening	TSQL	=azuresqldb-current || >=sql-server-2016 || >=sql-server-linux-2017 || =azuresqldb-mi-current || =fabric

OPEN (Transact-SQL)

[!INCLUDE SQL Server Azure SQL Database Azure SQL Managed Instance Fabricsqldb]

Opens a [!INCLUDEtsql] server cursor and populates the cursor by executing the [!INCLUDEtsql] statement specified on the DECLARE CURSOR or SET cursor_variable statement.

:::image type="icon" source="../../includes/media/topic-link-icon.svg" border="false"::: Transact-SQL syntax conventions

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 when the size of any row in the result set exceeds the maximum row size for [!INCLUDEssNoVersion] 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.

Note

[!INCLUDEssNoVersion] does not support generating keyset-driven or static [!INCLUDEtsql] cursors asynchronously. [!INCLUDEtsql] cursor operations such as OPEN or FETCH are batched, so there is no need for the asynchronous generation of [!INCLUDEtsql] cursors. [!INCLUDEssNoVersion] continues to support asynchronous keyset-driven or static application programming interface (API) server cursors where low latency OPEN is a concern, due to client round trips for each cursor operation.

Examples

The following example opens a cursor and fetches all the rows.

DECLARE Employee_Cursor CURSOR FOR  
SELECT LastName, FirstName  
FROM AdventureWorks2022.HumanResources.vEmployee  
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 (Transact-SQL)
@@CURSOR_ROWS (Transact-SQL)
DEALLOCATE (Transact-SQL)
DECLARE CURSOR (Transact-SQL)
FETCH (Transact-SQL)