SA0024 : Local cursor not closed |
![]() |
The topic describes the SA0024 analysis rule.

Local cursor not closed

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.

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

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

Rule has no parameters.

The rule does not need Analysis Context or SQL Connection.


Performance Rules, Bugs

There is no additional info for this rule.

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 |

Message | Line | Column | |
---|---|---|---|
1 | SA0024 : Local cursor ‘vend_cursor’ not closed. | 1 | 8 |
2 | SA0024 : Local cursor ‘vendor_cursor’ not closed. | 11 | 8 |
