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

DEALLOCATE

SQL Server 2000

Removes a cursor reference. When the last cursor reference is deallocated, the data structures comprising the cursor are released by Microsoft® SQL Server™.

Syntax

DEALLOCATE { { [ GLOBAL ] cursor_name } | @cursor_variable_name }

Arguments

cursor_name

Is the name of an already 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 and to the local cursor if GLOBAL is not specified.

@cursor_variable_name

Is the name of a cursor variable. @cursor_variable_name must be of type cursor.

Remarks

Statements that operate on cursors use either a cursor name or a cursor variable to refer to the cursor. DEALLOCATE removes the association between a cursor and the cursor name or cursor variable. If a name or variable is the last one referencing the cursor, the cursor is deallocated and any resources used by the cursor are freed. Scroll locks used to protect the isolation of fetches are freed at DEALLOCATE. Transaction locks used to protect updates, including positioned updates made through the cursor, are held until the end of the transaction.

The DECLARE CURSOR statement allocates and associates a cursor with a cursor name:

DECLARE abc SCROLL CURSOR FOR
SELECT * FROM authors

After a cursor name is associated with a cursor, the name cannot be used for another cursor of the same scope (GLOBAL or LOCAL) until this cursor has been deallocated.

A cursor variable is associated with a cursor using one of two methods:

  • By name using a SET statement that sets a cursor to a cursor variable:
    DECLARE @MyCrsrRef CURSOR
    SET @MyCrsrRef = abc
    
  • A cursor can also be created and associated with a variable without having a cursor name defined:
    DECLARE @MyCursor CURSOR
    SET @MyCursor = CURSOR LOCAL SCROLL FOR
    SELECT * FROM titles
    

A DEALLOCATE @cursor_variable_name statement removes only the reference of the named variable to the cursor. The variable is not deallocated until it goes out of scope at the end of the batch, stored procedure, or trigger. After a DEALLOCATE @cursor_variable_name statement, the variable can be associated with another cursor using the SET statement.

USE pubs
GO
DECLARE @MyCursor CURSOR
SET @MyCursor = CURSOR LOCAL SCROLL FOR
SELECT * FROM titles

DEALLOCATE @MyCursor

SET @MyCursor = CURSOR LOCAL SCROLL FOR
SELECT * FROM sales
GO

A cursor variable does not have to be explicitly deallocated. The variable is implicitly deallocated when it goes out of scope.

Permissions

DEALLOCATE permissions default to any valid user.

Examples

This script shows how cursors persist until the last name or until the variable referencing them has been deallocated.

USE pubs
GO
-- Create and open a global named cursor that
-- is visible outside the batch.
DECLARE abc CURSOR GLOBAL SCROLL FOR
SELECT * FROM authors
OPEN abc
GO
-- Reference the named cursor with a cursor variable.
DECLARE @MyCrsrRef1 CURSOR
SET @MyCrsrRef1 = abc
-- Now deallocate the cursor reference.
DEALLOCATE @MyCrsrRef1
-- Cursor abc still exists.
FETCH NEXT FROM abc
GO
-- Reference the named cursor again.
DECLARE @MyCrsrRef2 CURSOR
SET @MyCrsrRef2 = abc
-- Now deallocate cursor name abc.
DEALLOCATE abc
-- Cursor still exists, referenced by @MyCrsrRef2.
FETCH NEXT FROM @MyCrsrRef2
-- Cursor finally is deallocated when last referencing
-- variable goes out of scope at the end of the batch.
GO
-- Create an unnamed cursor.
DECLARE @MyCursor CURSOR
SET @MyCursor = CURSOR LOCAL SCROLL FOR
SELECT * FROM titles
-- The following statement deallocates the cursor
-- because no other variables reference it.
DEALLOCATE @MyCursor
GO

See Also

CLOSE

Cursors

DECLARE @local_variable

FETCH

OPEN

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