open-transact-sql.md

description	OPEN (Transact-SQL)
title	OPEN (Transact-SQL) | Microsoft Docs
ms.custom
ms.date	03/14/2017
ms.prod	sql
ms.prod_service	database-engine, sql-database
ms.reviewer
ms.technology	t-sql
ms.topic	language-reference
f1_keywords	OPEN_TSQL OPEN
dev_langs	TSQL
helpviewer_keywords	opening cursors cursors [SQL Server], opening populating cursors [SQL Server] OPEN statement Transact-SQL cursors, opening
ms.assetid	fd1c5e3b-6045-4a42-b646-3fca76e874c1
author	rothja
ms.author	jroth

OPEN (Transact-SQL)

[!INCLUDE SQL Server SQL Database]

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

Transact-SQL Syntax Conventions

Syntax

  
OPEN { { [ GLOBAL ] cursor_name } | cursor_variable_name }  

[!INCLUDEsql-server-tsql-previous-offline-documentation]

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 AdventureWorks2012.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)