SQL CLOSE

What is the SQL CLOSE statement used for?

Closes an open cursor and releases the associated resources.

Welcome to the Galaxy, Guardian!
You'll be receiving a confirmation email

Follow us on twitter :)

Oops! Something went wrong while submitting the form.

Compatible dialects for SQL CLOSE:

SQL CLOSE Full Explanation

CLOSE is a cursor-control statement defined in the SQL standard. A cursor must first be declared with DECLARE and opened with OPEN (or implicitly opened, depending on the dialect). When you issue CLOSE cursor_name, the database:- Releases any memory and locks tied to the cursor’s result set.- Invalidates the cursor position so further FETCH, MOVE, or UPDATE WHERE CURRENT commands fail.- Keeps the cursor definition in session scope, allowing an optional subsequent OPEN of the same cursor within the same transaction or connection.CLOSE does not commit or roll back the surrounding transaction. In some systems (e.g., SQL Server), you may additionally issue DEALLOCATE to remove the cursor definition entirely. Failing to close cursors can lead to excessive memory usage, open transactions, or lock contention.

SQL CLOSE Syntax

CLOSE cursor_name;

SQL CLOSE Parameters

Example Queries Using SQL CLOSE

-- Example in PostgreSQL
BEGIN;
DECLARE user_cur CURSOR FOR
    SELECT id, email FROM users WHERE active = true;
FETCH FROM user_cur;          -- work with the result set
CLOSE user_cur;               -- release resources
COMMIT;

-- Example in SQL Server
DECLARE user_cur CURSOR FOR
    SELECT id, email FROM dbo.users WHERE active = 1;
OPEN user_cur;
FETCH NEXT FROM user_cur;     -- process rows
CLOSE user_cur;               -- free result set memory but keep definition
DEALLOCATE user_cur;          -- remove the cursor object

Expected Output Using SQL CLOSE

The specified cursor is closed
Subsequent FETCH statements on that cursor return an error such as "cursor is not open
" No result set is returned by the CLOSE statement itself

Use Cases with SQL CLOSE

Freeing resources after iterating through a result set with a server-side cursor
Preventing lock retention in long-running transactions
Preparing the same cursor for reopening with different parameters in dynamic SQL workflows

Common Mistakes with SQL CLOSE

Forgetting to issue CLOSE, leading to leaked cursors and memory bloat
Attempting FETCH after CLOSE, resulting in runtime errors
Confusing CLOSE with DEALLOCATE in SQL Server; DEALLOCATE fully removes the cursor object
Assuming CLOSE commits the transaction (it does not)

First Introduced In

SQL-92

Frequently Asked Questions

What happens if I forget to close a cursor?

The cursor stays active for the session or transaction, consuming memory and potentially holding locks. Over time this can degrade performance and exhaust available cursors.

Do I always need to OPEN a cursor before CLOSE?

Yes. CLOSE only works on cursors that are currently open. Declaring a cursor does not automatically open it in all dialects.

Can I fetch rows after issuing CLOSE?

No. FETCH, MOVE, or positioned UPDATE statements referencing that cursor will raise an error like "cursor is not open".

Is CLOSE part of transaction control?

Not directly. CLOSE affects only cursor state. Use COMMIT or ROLLBACK to terminate or undo the transaction enclosing the cursor work.