Click or drag to resize

SA0010 : Use TRY..CATCH or check the @@ERROR variable after executing data manipulation statement

The topic describes the SA0010 analysis rule.

Message

Use TRY..CATCH or check the @@ERROR variable after executing data manipulation statement

Description

The rule checks for SELECT INTO,INSERT,DELETE and UPDATE statements which are neither inside TRY..CATCH block nor they are checked if completed successfully using the @ERROR variable.

This check is important, because, by default, SQL Server will not rollback all the previous changes within a transaction if a particular statement fails and setting XACT_ABORT is not ON.

Scope

The rule has a Batch scope and is applied only on the SQL script.

Parameters
NameDescriptionDefault Value
RuleSuppressMark

Suppress rule reporting a rule violation in case a comment containing the value of this parameter is found close to the source of violation.

IGNORE:SA0010

ConsiderXactAbortSetting

When the XACT_ABORT setting is set to ON before the checked statements, the statements are ignored.

yes

IgnoreStatementsInExplicitTransaction

Ignore the statements which are inside explicit transaction.

yes

IgnoreInsideDMLTrigger

The parameter specifies if the not handled statemetns to be ignored when appear in a DML trigger.

yes

Remarks

The rule does not need Analysis Context or SQL Connection.

Categories

Design Rules

Additional Information

There is no additional info for this rule.

Example Test Script
SQL
 1-- The INSERT statement is not checked for failure.
 2INSERT INTO TABLE1 
 3VALUES ( 1,'PMZ0012','1000AA','2010-01-01 00:00:00.000')
 4
 5-- The DELETE statement is not checked for failure.
 6DELETE FROM TABLE1 
 7WHERE ID = 124
 8
 9-- The UPDATE statement uses @@ERROR to check for a check constraint violation (error #547).
10UPDATE HumanResources.EmployeePayHistory
11SET PayFrequency = 4
12WHERE EmployeeID = 1;
13
14IF @@ERROR = 547 PRINT N'A check constraint violation occurred.';
15
16-- The DELETE statement is inside TRY/CATCH block and will pass the rule check.
17BEGIN TRY
18        DELETE FROM TABLE1 
19        WHERE ID = 221
20END TRY
21BEGIN CATCH
22        SELECT         ERROR_NUMBER() AS ErrorNumber
23                        ,ERROR_SEVERITY() AS ErrorSeverity
24                        ,ERROR_STATE() AS ErrorState
25                        ,ERROR_PROCEDURE() AS ErrorProcedure
26                        ,ERROR_LINE() AS ErrorLine
27                        ,ERROR_MESSAGE() AS ErrorMessage;
28
29END CATCH
30
31-- The checking of the DELETE statement below suppressed.
32DELETE FROM TABLE1  -- IGNORE:SA0010
33WHERE ID = 124
34
35SELECT * INTO Table2 FROM Table1;
36
37MERGE INTO A_Table WITH (FASTFIRSTROW)
38USING 
39    (SELECT 'data_searched' AS Search_Col FROM B_TABLE) AS SRC
40    ON A_Table.Data = SRC.Search_Col
41WHEN MATCHED THEN
42    UPDATE SET Data = 'data_searched_updated'
43WHEN NOT MATCHED THEN
44    INSERT (Data) VALUES (SRC.Search_Col); 
45
46
47BEGIN TRAN
48
49SELECT * INTO Table2 FROM Table1;
50
51SELECT * INTO Table2 FROM Table2;
52
53COMMIT TRAN
54
55SELECT * INTO Table2 FROM Table2;
56
57DECLARE @Table TABLE ( ID int NOT NULL, Txt NVARCHAR(100))
58
59DELETE FROM @Table WHERE ID > 10
60
61INSERT INTO @Table (ID, Txt) VALUES (1,'Text')
62
63UPDATE @Table SET Txt = 'Txt 5' WHERE ID = 5

Analysis Results

 MessageLineColumn
1SA0010 : Use TRY..CATCH or check the @@ERROR variable after executing data manipulation statement.20
2SA0010 : Use TRY..CATCH or check the @@ERROR variable after executing data manipulation statement.60
3SA0010 : Use TRY..CATCH or check the @@ERROR variable after executing data manipulation statement.350
4SA0010 : Use TRY..CATCH or check the @@ERROR variable after executing data manipulation statement.370
5SA0010 : Use TRY..CATCH or check the @@ERROR variable after executing data manipulation statement.550
See Also

Other Resources