SA0257 : The cursor declaration does not fit the performed cursor operations |
 |
The topic describes the SA0257 analysis rule.
MessageThe cursor declaration does not fit the performed cursor operations
DescriptionThe rule checks cursor declarations and reports when the cursor is not actually used, should be declared as update-able, can be declared as READ_ONLY or can be declared as FORWARD_ONLY.
– A cursor is reported as not used when it is not used in any FETCH, UPDATE or DELETE statements.
– A cursor should be made update-able when is it is used in UPDATE and DELETE statements.
– A cursor can be made READ_ONLY when it is not used in UPDATE and DELETE statements.
– A cursor can be made FORWARD_ONLY when it is only used with FETCH NEXT option.
How to fixReview the cursor declaration, and remove unnecessary or add more specific cursor options as suggested by the particular rule violation.
ScopeThe rule has a Batch scope and is applied only on the SQL script.
ParametersRule has no parameters.
RemarksThe rule does not need Analysis Context or SQL Connection.
Effort To FixCategoriesDesign Rules, Bugs
Additional InformationExample Test SQL|
SQL
1DECLARE read_only_cursor CURSOR 2 FOR SELECT * FROM Purchasing.Vendor 3 FOR UPDATE OF Name, LastName 4OPEN read_only_cursor 5FETCH NEXT FROM read_only_cursor 6FETCH LAST FROM read_only_cursor 7CLOSE read_only_cursor 8DEALLOCATE read_only_cursor 9 10DECLARE updated_cursor CURSOR 11 FOR SELECT * FROM Purchasing.Vendor where Name like 'J%' 12 FOR UPDATE OF Name, LastName 13OPEN updated_cursor 14FETCH NEXT FROM updated_cursor 15DELETE FROM Purchasing.Vendor 16WHERE CURRENT OF updated_cursor; 17CLOSE updated_cursor 18DEALLOCATE updated_cursor 19 20DECLARE read_only_forward_only_cursor CURSOR 21 FOR SELECT * FROM Purchasing.Vendor 22 FOR UPDATE OF Name, LastName 23OPEN read_only_forward_only_cursor 24FETCH NEXT FROM read_only_forward_only_cursor 25CLOSE read_only_forward_only_cursor 26DEALLOCATE read_only_forward_only_cursor 27 28DECLARE unused_cursor CURSOR FOR SELECT * FROM Purchasing.Vendor for read only |
Analysis Results| Message | Line | Column | |
|---|---|---|---|
| 1 | SA0257 : The cursor [read_only_cursor] can be made READ_ONLY as it is not used in UPDATE and DELETE statements. | 1 | 8 |
| 2 | SA0257 : The cursor [updated_cursor] is not declared as updateable even it is used in UPDATE and DELETE statements. | 10 | 8 |
| 3 | SA0257 : The cursor [read_only_forward_only_cursor] can be made READ_ONLY as it is not used in UPDATE and DELETE statements. | 20 | 8 |
| 4 | SA0257 : The cursor [read_only_forward_only_cursor] can be declared as FORWARD_ONLY as it is only used with FETCH NEXT option. | 20 | 8 |
| 5 | SA0257 : The cursor [unused_cursor] is not used in any FETCH, UPDATE or DELETE statements. | 28 | 8 |
See Also