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

Introduction

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:

BEGIN TRANSACTION;
DELETE FROM Orders WHERE OrderID = 10248;
-- Imagine an error occurs here
INSERT INTO Logs (LogMessage) VALUES ('Order deleted');
COMMIT;

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:

BEGIN TRANSACTION;
BEGIN TRY
    DELETE FROM Orders WHERE OrderID = 10248;
    INSERT INTO Logs (LogMessage) VALUES ('Order deleted');
    COMMIT;
END TRY
BEGIN CATCH
    ROLLBACK;
    -- Handle error, for instance, logging it
    INSERT INTO ErrorLogs (ErrorNumber, ErrorMessage)
    VALUES (ERROR_NUMBER(), ERROR_MESSAGE());
END 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.

Additional Information

There is no additional info for this rule.

Example Test SQL

-- The INSERT statement is not checked for failure.
INSERT INTO TABLE1
VALUES ( 1,'PMZ0012','1000AA','2010-01-01 00:00:00.000')

-- The DELETE statement is not checked for failure.
DELETE FROM TABLE1
WHERE ID = 124

-- The UPDATE statement uses @@ERROR to check for a check constraint violation (error #547).
UPDATE HumanResources.EmployeePayHistory
SET PayFrequency = 4
WHERE EmployeeID = 1;

IF @@ERROR = 547 PRINT N'A check constraint violation occurred.';

-- The DELETE statement is inside TRY/CATCH block and will pass the rule check.
BEGIN TRY
  DELETE FROM TABLE1
  WHERE ID = 221
END TRY
BEGIN CATCH
  SELECT   ERROR_NUMBER() AS ErrorNumber
      ,ERROR_SEVERITY() AS ErrorSeverity
      ,ERROR_STATE() AS ErrorState
      ,ERROR_PROCEDURE() AS ErrorProcedure
      ,ERROR_LINE() AS ErrorLine
      ,ERROR_MESSAGE() AS ErrorMessage;

END CATCH

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

Analysis Rules