Powered by Blogger.

Saturday, February 15, 2014

SET XACT_ABORT ON in sql server




In this post we will discuss about SET XACT_ABORT in sql server 2008. Also you can check out my previous posts on:

- Asp.Net MVC interview questions and answers

- File upload and thumbnail creation in Asp.Net

- JavaScript Functions example

SET XACT_ABORT specifies whether sql server automatically rools back the current transction if a sql statement raises a runtime error.

This uses mostly when you are using transctions in sql server stored procedures.

SET XACT_ABORT can be set to ON or OFF, If it sets to ON, then if a T-sql statement raises a run-time error, the entire transaction is terminated and rolled back.

But if you set this to OFF, then only the T-SQL statement that raised the error is rolled back and the transaction continues processing.

You can write the statement before the BEGIN TRANSACTION statement like below:

USE [TestDB]
GO

SET ANSI_NULLS ON
GO

SET QUOTED_IDENTIFIER ON
GO

 CREATE PROCEDURE [dbo].[Employees]
 @Name nvarchar (256) ,
 @Age Int

 AS
 BEGIN
 BEGIN TRY

SET XACT_ABORT ON

 BEGIN TRANSACTION

--*************************************
 INSERT INTO Employees  (Name,Age) VALUES ( @Name, @Age )
--*************************************
 COMMIT TRANSACTION
      RETURN 1
-- Sucessful
 END TRY
 BEGIN CATCH
RETURN -111
 END CATCH
END

GO



0 comments

Post a Comment