Click or drag to resize

SA0254 : Invalid operation due to cursor closed or not declared

The topic describes the SA0254 analysis rule.

Message

Invalid operation due to cursor closed or not declared

Description

The rule checks T-SQL code for statements using cursors and reports when the referenced cursor is not in the expected state.

The cursor operation statement will cause error, because there is no cursor declaration found in the current scope or the cursor is not previously opened.

Ensure the cursor is declared and in open state before executing the operations using the cursor.

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

Design Rules, New Rules

Additional Information
Example Test Script
SQL
 1DECLARE vend_cursor CURSOR
 2    FOR SELECT * FROM Purchasing.Vendor
 3
 4OPEN vend_cursor
 5CLOSE vend_cursor
 6FETCH NEXT FROM vend_cursor
 7
 8DECLARE @vendor_id int, @vendor_name nvarchar(50),
 9    @message varchar(80), @product nvarchar(50)
10
11PRINT '-------- Vendor Products Report --------'
12
13DECLARE vendor_cursor CURSOR FOR 
14SELECT VendorID, Name
15FROM Purchasing.Vendor
16WHERE PreferredVendorStatus = 1
17ORDER BY VendorID
18
19OPEN vendor_cursor1
20
21FETCH NEXT FROM vendor_cursor 
22INTO @vendor_id, @vendor_name
23
24WHILE @@FETCH_STATUS = 0
25BEGIN
26    PRINT ' '
27    SELECT @message = '----- Products From Vendor: ' + 
28        @vendor_name
29
30    PRINT @message
31
32    -- Declare an inner cursor based   
33    -- on vendor_id from the outer cursor.
34
35    DECLARE product_cursor CURSOR FOR 
36    SELECT v.Name
37    FROM Purchasing.ProductVendor pv, Production.Product v
38    WHERE pv.ProductID = v.ProductID AND
39    pv.VendorID = @vendor_id  -- Variable value from the outer cursor
40
41    OPEN product_cursor
42    FETCH NEXT FROM product_cursor INTO @product
43
44    IF @@FETCH_STATUS <> 0 
45        PRINT '         <<None>>'     
46
47    WHILE @@FETCH_STATUS = 0
48    BEGIN
49
50        SELECT @message = '         ' + @product
51        PRINT @message
52        FETCH NEXT FROM product_cursor INTO @product
53        END
54
55    CLOSE product_cursor
56    DEALLOCATE product_cursor
57        -- Get the next vendor.
58    FETCH NEXT FROM vendor_cursor 
59    INTO @vendor_id, @vendor_name
60END 
61-- CLOSE vendor_cursor
62DEALLOCATE vendor_cursor
63
64DECLARE abc CURSOR LOCAL FOR  
65    SELECT c1, c2   
66    FROM dbo.Table1;  
67OPEN abc;  
68FETCH abc;  
69UPDATE dbo.Table1   
70SET c2 = c2 + d2   
71FROM dbo.Table2   
72WHERE CURRENT OF abc;
73CLOSE abc
74
75
76DECLARE complex_cursor CURSOR FOR  
77    SELECT a.BusinessEntityID  
78    FROM HumanResources.EmployeePayHistory AS a  
79    WHERE RateChangeDate <>   
80         (SELECT MAX(RateChangeDate)  
81          FROM HumanResources.EmployeePayHistory AS b  
82          WHERE a.BusinessEntityID = b.BusinessEntityID) ;  
83OPEN complex_cursor;  
84FETCH FROM complex_cursor;  
85DELETE FROM HumanResources.EmployeePayHistory  
86WHERE CURRENT OF complex_cursor;  
87CLOSE complex_cursor;  
88DEALLOCATE complex_cursor;  
89
90DECLARE vend_cursor CURSOR
91    FOR SELECT * FROM Purchasing.Vendor

Analysis Results

 MessageLineColumn
1SA0254 : The referenced cursor [vend_cursor] is closed earlier.616
2SA0254 : The referenced cursor [vendor_cursor1] is not declared in the current scope.195
3SA0254 : The referenced cursor [vendor_cursor] is not opened.2116
4SA0254 : The referenced cursor [vendor_cursor] is not opened.5820
See Also

Other Resources