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

Executing SELECT INTO, INSERT, DELETE, and UPDATE statements without proper error handling can lead to partial data modifications, which may cause inconsistencies and data integrity issues in the database.

Description

In SQL Server, when modifying data using SELECT INTO, INSERT, DELETE, or UPDATE statements, it’s important to handle errors to prevent inconsistencies. Without proper error handling, such as wrapping statements in a TRY..CATCH block or checking for success using the @ERROR variable, failed statements do not automatically rollback previous changes. This can leave the database in a partially modified state.

Example of problematic query:

SQL
Copy
1BEGIN TRANSACTION;
2DELETE FROM Orders WHERE OrderID = 10248;
3-- Imagine an error occurs here
4INSERT INTO Logs (LogMessage) VALUES ('Order deleted');
5COMMIT;

In this example, if the INSERT statement fails and XACT_ABORT is not set to ON, SQL Server will not rollback the DELETE, potentially leading to data integrity issues.

  • Potential data inconsistencies due to unhandled errors.

  • Lack of automatic rollback can lead to incomplete transactions.

  • Additional error handling logic is needed to ensure data integrity.

How to fix

Implement error handling to ensure data consistency when executing data modification statements like SELECT INTO, INSERT, DELETE, and UPDATE.

Follow these steps to address the issue:

  1. Wrap the data modification statements in a TRY..CATCH block to capture and handle any errors that occur.

  2. Set XACT_ABORT to ON to automatically rollback the transaction if an error arises.

  3. Optionally, use the @ERROR variable within a CATCH block to log or manage errors specifically.

Example of a corrected query using TRY..CATCH:

SQL
Copy
 1BEGIN TRANSACTION;
 2BEGIN TRY
 3    DELETE FROM Orders WHERE OrderID = 10248;
 4    INSERT INTO Logs (LogMessage) VALUES ('Order deleted');
 5    COMMIT;
 6END TRY
 7BEGIN CATCH
 8    ROLLBACK;
 9    -- Handle error, for instance, logging it
10    INSERT INTO ErrorLogs (ErrorNumber, ErrorMessage)
11    VALUES (ERROR_NUMBER(), ERROR_MESSAGE());
12END CATCH;

Scope

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

Parameters
Name Description Default Value
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 statements to be ignored when appear in a DML trigger.

yes
Remarks

The rule does not need Analysis Context or SQL Connection.

Effort To Fix
13 minutes per issue.
Categories

Design Rules, Bugs

Additional Information

There is no additional info for this rule.

Example Test SQL
SQL
Copy
 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
31BEGIN TRAN
32-- The SELECT INTO statement insinde explicit transaction
33SELECT * INTO Table2 FROM Table1;
34COMMIT TRAN
Analysis Results
  Message Line Column
1 SA0010 : Use TRY..CATCH or check the @@ERROR variable after executing data manipulation statement. 2 0
2 SA0010 : Use TRY..CATCH or check the @@ERROR variable after executing data manipulation statement. 6 0
See Also

Other Resources

Analysis Rules