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.

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

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:
-
Wrap the data modification statements in a TRY..CATCH block to capture and handle any errors that occur.
-
Set XACT_ABORT to ON to automatically rollback the transaction if an error arises.
-
Optionally, use the @ERROR variable within a CATCH block to log or manage errors specifically.
Example of a corrected query using TRY..CATCH:
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;

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

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 |

The rule does not need Analysis Context or SQL Connection.


Design Rules, Bugs

There is no additional info for this rule.

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 31BEGIN TRAN 32-- The SELECT INTO statement insinde explicit transaction 33SELECT * INTO Table2 FROM Table1; 34COMMIT TRAN |

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 |
