SA0024 : Local cursor not closed

Introduction

Cursors in T-SQL should be closed promptly to prevent unnecessary resource locking.

Description

In SQL Server, cursors allow row-by-row iteration over a result set, which can be useful for complex logic that can’t be easily achieved with set-based operations. However, if cursors remain open unnecessarily, they can hold locks on database tables or views, leading to potential performance degradation and blocking issues.

Example of a cursor that is left open:

DECLARE cur CURSOR FOR
SELECT * FROM TableName;
OPEN cur;
-- Cursor is not closed explicitly

This query leaves the cursor open, which can impact other operations. It is a best practice to close the cursor explicitly when it is no longer in use to release the resources and locks.

Open cursors unnecessarily hold locks, leading to blocking and increased contention.
Failure to close cursors can cause memory and resource leaks, adversely affecting server performance.

How to fix

Explicitly close the cursor when it is no longer needed to release resources and prevent performance issues.

Follow these steps to address the issue:

1.Declare and open the cursor as needed in your T-SQL code using DECLARE CURSOR and OPEN statements.

2.Use the cursor to perform the necessary row-by-row operations.

3.Once the cursor operations are complete, explicitly close the cursor using the CLOSE statement to release any locks.

4.Deallocate the cursor with the DEALLOCATE statement to free memory resources.

For example:

-- Example of a corrected query using a cursor
DECLARE cur CURSOR FOR
SELECT * FROM TableName;

OPEN cur;

-- Perform operations with the cursor

CLOSE cur;
DEALLOCATE cur;

Scope

The rule has a Batch scope and is applied only on the SQL script.

Parameters

Rule has no parameters.

Remarks

The rule does not need Analysis Context or SQL Connection.

Effort To Fix

2 minutes per issue.

Additional Information

There is no additional info for this rule.

Example Test SQL

DECLARE objects_cursor CURSOR FOR
SELECT object_id, name
FROM sys.objects where type = 'U'

OPEN objects_cursor

--CLOSE objects_cursor

DEALLOCATE objects_cursor

Analysis Results

	Message	Line	Column
1	SA0024 : Local cursor ‘objects_cursor’ not closed.	1	8

Analysis Rules