SA0024 : Local cursor not closed

The topic describes the SA0024 analysis rule.

Message

Local cursor not closed

Description

The rule checks if any local cursor is closed until the end of the batch.

Because when open, the cursor still holds locks on referred-to-tables or views, you should explicitly close it as soon as it is no longer needed.

How to fix

Explicitly close the cursor as soon as it is no longer needed.

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.
Categories

Performance Rules, Bugs

Additional Information

There is no additional info for this rule.

Example Test SQL
SQL
 1DECLARE vend_cursor CURSOR
 2    FOR SELECT * FROM Purchasing.Vendor
 3OPEN vend_cursor
 4FETCH NEXT FROM vend_cursor
 5
 6DECLARE @vendor_id int, @vendor_name nvarchar(50),
 7    @message varchar(80), @product nvarchar(50)
 8
 9PRINT '-------- Vendor Products Report --------'
10
11DECLARE vendor_cursor CURSOR FOR 
12SELECT VendorID, Name
13FROM Purchasing.Vendor
14WHERE PreferredVendorStatus = 1
15ORDER BY VendorID
16
17OPEN vendor_cursor
18
19FETCH NEXT FROM vendor_cursor 
20INTO @vendor_id, @vendor_name
21
22WHILE @@FETCH_STATUS = 0
23BEGIN
24    PRINT ' '
25    SELECT @message = '----- Products From Vendor: ' + 
26        @vendor_name
27
28    PRINT @message
29
30    -- Declare an inner cursor based   
31    -- on vendor_id from the outer cursor.
32
33    DECLARE product_cursor CURSOR FOR 
34    SELECT v.Name
35    FROM Purchasing.ProductVendor pv, Production.Product v
36    WHERE pv.ProductID = v.ProductID AND
37    pv.VendorID = @vendor_id  -- Variable value from the outer cursor
38
39    OPEN product_cursor
40    FETCH NEXT FROM product_cursor INTO @product
41
42    IF @@FETCH_STATUS <> 0 
43        PRINT '         <<None>>'     
44
45    WHILE @@FETCH_STATUS = 0
46    BEGIN
47
48        SELECT @message = '         ' + @product
49        PRINT @message
50        FETCH NEXT FROM product_cursor INTO @product
51        END
52
53    CLOSE product_cursor
54    DEALLOCATE product_cursor
55        -- Get the next vendor.
56    FETCH NEXT FROM vendor_cursor 
57    INTO @vendor_id, @vendor_name
58END 
59-- CLOSE vendor_cursor
60DEALLOCATE vendor_cursor

Analysis Results
  Message Line Column
1 SA0024 : Local cursor ‘vend_cursor’ not closed. 1 8
2 SA0024 : Local cursor ‘vendor_cursor’ not closed. 11 8
See Also

Other Resources