DEALLOCATE

SQL Server

Example

DECLARE @MyCursor CURSOR;
SET @MyCursor = CURSOR FOR
SELECT EmployeeID, FirstName, LastName
FROM Employees;
OPEN @MyCursor;
CLOSE @MyCursor;
DEALLOCATE @MyCursor;

Output

Command(s) completed successfully.

Explanation

In this example, a cursor named @MyCursor is declared and set to a select statement querying EmployeeID, FirstName, & LastName from the Employees table.

The OPEN command opens the cursor to use for data manipulation.

The CLOSE command closes the cursor, notifying SQL Server that resources associated with the cursor can no longer be accessed.

Finally, the DEALLOCATE command is used to remove the cursor definition and resources associated with it.

After deallocating a cursor, it no longer exists and cannot be closed or deallocated again. Trying to do so will result in an error message. Hence, the output message in successful scenarios will be ‘Command(s) completed successfully’ after DEALLOCATE.

PostgreSQL

Example

BEGIN;

DECLARE customers_cursor CURSOR FOR
SELECT * FROM customers;

FETCH NEXT FROM customers_cursor;

DEALLOCATE customers_cursor;

END;

Output

ROLLBACK

Explanation

The example demonstrates the DEALLOCATE command in PostgreSQL. The DEALLOCATE is used to release a previously declared cursor. Initially, a cursor named customers_cursor is declared and populated with all records from the customers table. After fetching the next record from customers_cursor, the cursor is deallocated.