TSQL Tip of the Day

Tip #1
Why use XACT_ABORT Feature ?

I had a problem in one of my projects. For one of the procedure calls involving insert and update into multiple tables, only the initial insert was present in database tables and rest of transaction does not seem to have completed. On Further investigation figured out from the C# code when the timeout occurs transaction is aborted. From Database we did not use XACT_ABORT feature to rollback the transaction.

XACT_ABORT Example referred to post in MSDN

Reusing the same tables I tried to have it in a stored procedure instead of a provided insert statements. I am using exception handling suggested in blog - Exception Handling best practices provided by Adam. Rethrow procedure is provided in the example

Below is Test Procedure

IF OBJECT_ID ('AddT1T2TableDetails') IS NOT NULL DROP PROCEDURE AddT1T2TableDetails
GO
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE PROCEDURE [dbo].[AddT1T2TableDetails]     
(              
  @InputT1XML xml,
  @InputT2XML xml
)             
AS              
BEGIN                       
/******************************************************************************************************************************************             
** File  : AddT1T2TableDetails             
** Purpose : Inserts record into T1 and T2 Table             
**             
** Returns : ZERO incase of --> Success             
**    ONE incase of --> Failure             
** OUTPUT :             
** Called by: TestProc
** Calls : None
********************************************************************************************************************************************/             
 SET NOCOUNT ON             
 SET XACT_ABORT ON
 BEGIN TRY
      BEGIN TRAN
      INSERT INTO T1
      SELECT c.value('./Number[1]', 'Integer')
      FROM @InputT1XML.nodes('Request/Values/Value') DataT1(c)

      INSERT INTO T2

      SELECT c.value('./Number[1]', 'Integer')
      FROM @InputT2XML.nodes('Request/Values/Value') DataT2(c)
      COMMIT TRAN
      Return 0
 END TRY
 BEGIN CATCH
      EXEC Rethrow
      Return 1
 END CATCH
END

Tested the proc with below test cases

--Positive Case
DECLARE @InputT1XML XML, @InputT2XML XML, @Retval INT
SET @InputT1XML = '<Request>
<Values>
<Value><Number>1</Number></Value>
<Value><Number>2</Number></Value>
<Value><Number>3</Number></Value>
</Values>
</Request>'

SET @InputT2XML = '<Request>

<Values>
<Value><Number>1</Number></Value>
<Value><Number>2</Number></Value>
<Value><Number>3</Number></Value>
</Values>
</Request>'
EXEC @RetVal = dbo.AddT1T2TableDetails @InputT1XML, @InputT2XML
SELECT @RetVal

SELECT * FROM T1
SELECT * FROM T2

Data Successfully Inserted  

Negative Case
--Negative Case
DECLARE @InputT1XML XML, @InputT2XML XML, @Retval INT
SET @InputT1XML = '<Request>
<Values>
<Value><Number>4</Number></Value>
<Value><Number>5</Number></Value>
<Value><Number>6</Number></Value>
</Values>
</Request>'

SET @InputT2XML = '<Request>

<Values>
<Value><Number>4</Number></Value>
<Value><Number>5</Number></Value>
<Value><Number>10</Number></Value>
</Values>
</Request>'
EXEC @RetVal =  dbo.AddT1T2TableDetails @InputT1XML, @InputT2XML 
SELECT @RetVal

Retval is 1 when you execute the proc. Transaction is rolled  back.

Learnings - Use SET XACT_ABORT ON in your stored procedure logic when it involves multiple transactions.

More Reads -  Use XACT ABORT to roll back non trappable error transactions

Happy Reading!!!