Click or drag to resize

SA0025 : Local cursor not explicitly deallocated

The topic describes the SA0025 analysis rule.

Message

Local cursor not explicitly deallocated

Description

The rule checks if cursor name is deallocated before the end of the batch using the DEALLOCATE statement.

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.

Categories

Performance Rules

Additional Information

There is no additional info for this rule.

Example Test Script
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 
59CLOSE vendor_cursor
60
61-- DEALLOCATE vendor_cursor

Analysis Results

 MessageLineColumn
1SA0025 : Local cursor 'vend_cursor' not explicitly deallocated.18
2SA0025 : Local cursor 'vendor_cursor' not explicitly deallocated.118
See Also

Other Resources