T-SQL Analyzer

# SQL Server Rule: SRD0069 | | | |----|----| | Assembly | SqlServer.Rules | | Namespace | SqlServer.Rules.Design | | Class | UseXactAbortWithExplicitTransactionsRule | ## Rule Information | | | |----|----| | Id | SRD0069 | | Friendly Name | Xact_Abort On | | Category | Design | | Ignorable | true | | Applicable Types | Procedure | ## Description Use SET XACT_ABORT ON in stored procedures with explicit transactions. ## Summary Use SET XACT_ABORT ON in stored procedures with explicit transactions ### Examples ```sql CREATE PROCEDURE dbo.CreateProcedureExplicitTransaction AS SET NOCOUNT ON; BEGIN TRANSACTION; INSERT INTO [dbo].TestTableSSDT (Col1) VALUES ('TestValue'); COMMIT TRANSACTION; -- SRD0069 ``` ```sql CREATE PROCEDURE dbo.CreateProcedureExplicitTransaction2 AS SET NOCOUNT ON; SET XACT_ABORT ON; BEGIN TRANSACTION; INSERT INTO [dbo].TestTableSSDT (Col1) VALUES ('TestValue'); COMMIT TRANSACTION; -- SRD0069 ``` ```sql CREATE TABLE dbo.bad (absolute INT CONSTRAINT [PK_bad] PRIMARY KEY); GO CREATE TABLE dbo.good (absalute INT CONSTRAINT [PK_good] PRIMARY KEY); GO -- SRD0069 ``` ### Remarks This rule scans stored procedures to ensure they SET XACT_ABORT to ON at the beginning. When SET XACT_ABORT is ON, if a Transact-SQL statement raises a run-time error, the entire transaction is terminated and rolled back. This setting prevents transactions from remaining open when certain errors occur. _{^{Generated by a tool}}