DF023: Cursor is not closed and/or deallocated.

The topic describes the DF023 T-SQL code analysis rule.

Category

BEST PRACTICE

Message

The cursor is not closed and/or deallocated.

Description

The cursor is opened but not closed and/or deallocated. Locally opened cursors should be explicitly closed to preserve resources.

Additional information

Leaving cursors open can lead to resource contention and memory leaks, as the database engine continues to hold onto resources associated with the cursor until it is explicitly closed and deallocated.

Noncompliant code example

DECLARE cur CURSOR LOCAL FOR
SELECT CustomerID FROM dbo.Customer
OPEN cur
FETCH NEXT FROM cur INTO @CustomerId

Compliant solution

DECLARE cur CURSOR LOCAL FORWARD_ONLY FOR
SELECT CustomerID FROM dbo.Customer
OPEN cur
FETCH NEXT FROM cur INTO @CustomerId
DEALLOCATE cur