Click or drag to resize

SA0257 : The cursor declaration does not fit the performed cursor operations

The topic describes the SA0257 analysis rule.

Message

The cursor declaration does not fit the performed cursor operations

Description

The 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.

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 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

 MessageLineColumn
1SA0257 : The cursor [read_only_cursor] can be made READ_ONLY as it is not used in UPDATE and DELETE statements.18
2SA0257 : The cursor [updated_cursor] is not declared as updateable even it is used in UPDATE and DELETE statements.108
3SA0257 : The cursor [read_only_forward_only_cursor] can be made READ_ONLY as it is not used in UPDATE and DELETE statements.208
4SA0257 : The cursor [read_only_forward_only_cursor] can be declared as FORWARD_ONLY as it is only used with FETCH NEXT option.208
5SA0257 : The cursor [unused_cursor] is not used in any FETCH, UPDATE or DELETE statements.288
See Also

Other Resources