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.

How to fix

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.

Effort To Fix
8 minutes per issue.
Categories

Explicit Rules, Code Smells

Additional Information

There is no additional info for this rule.

Example Test SQL
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