SA0254 : Invalid operation due to cursor closed or not declared

 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