EX0005 : Check script for data modifying statements – INSERT,UPDATE,DELETE or EXECUTE

The topic describes the EX0005 analysis rule.

Message

Check script for data modifying statements – INSERT,UPDATE,DELETE or EXECUTE

Description

Check script for data modifying statements – INSERT,UPDATE,DELETE or EXECUTE.

Note Note

Any modification of table variables are ignored by the rule.

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

Explicit Rules, Code Smells

Additional Information

There is no additional info for this rule.

Example Test Script
SQL
 1-- This INSERT statement will cause analysis rule violation
 2INSERT INTO Cities (Location)
 3VALUES ( dbo.CreateNewPoint(x, y) );
 4
 5-- This INSERT statement will cause analysis rule violation
 6INSERT INTO Cities (Location)
 7VALUES ( CONVERT(Point, '12.3:46.2') );
 8
 9-- This INSERT statement will cause analysis rule violation
10INSERT INTO dbo.Table1 VALUES(1, 10.0), (1, 20.0), (1, 0.0);
11
12
13-- This UPDATE statement will cause analysis rule violation
14UPDATE dbo.Table2 
15SET dbo.Table2.ColB = dbo.Table2.ColB + dbo.Table1.ColB
16FROM dbo.Table2 
17    INNER JOIN dbo.Table1 
18    ON (dbo.Table2.ColA = dbo.Table1.ColA);
19
20
21SELECT ColA, ColB 
22FROM dbo.Table2;
23
24DECLARE abc CURSOR LOCAL FOR
25    SELECT c1, c2 
26    FROM dbo.Table1;
27OPEN abc;
28FETCH abc;
29
30-- This UPDATE statement will cause analysis rule violation
31UPDATE dbo.Table1 
32SET c2 = c2 + d2 
33FROM dbo.Table2 
34WHERE CURRENT OF abc;
35
36-- This DELETE statement will cause analysis rule violation
37DELETE FROM dbo.Table1 
38WHERE
39ColA  > 4556

Analysis Results

  Message Line Column
1 EX0005 : Script contains INSERT data modifying statement. 2 0
2 EX0005 : Script contains INSERT data modifying statement. 6 0
3 EX0005 : Script contains INSERT data modifying statement. 10 0
4 EX0005 : Script contains UPDATE data modifying statement. 14 0
5 EX0005 : Script contains UPDATE data modifying statement. 31 0
6 EX0005 : Script contains DELETE data modifying statement. 37 0
See Also

Other Resources