October 18, 2011 at 5:29 am
RDBMS : MSSQL 2005
I am writing stored procedure and using transaction control with Xact_Abort. I want to know how can I check the status of success or failure with Xact_Abort? I want to return error code in case of failure.
The code is given below
.......
.......
BEGIN
SET XACT_ABORT ON
Begin Transaction
--==========================================
-- Insert Customer Info
---=========================================
INSERT
INTO dbo.tblCustomer
(
Name,
[Description],
Title,
IsNewProductNotify
)
values
(
@SiteName,
@Description,
@PageTitle,
@IsAutoProductUpdate
);
-- Storing tblCustomer identity value in vairable for subsequest insert
SELECT @SiteIdent=IDENT_CURRENT('dbo.tblCustomer');
INSERT
INTO dbo.tblPage
(
LogoImage,
WelcomeMessage,
SiteId
)
VALUES
(
@HeaderBanner,
@WelcomeMessae,
@SiteIdent
);
INSERT
INTO dbo.tblFeature
(
MetaDescription,
MetaKeyword,
SiteId
)
VALUES
(
@MetaKeywords,
@MetaDescription,
@SiteIdent
);
Commit Transaction
SET XACT_ABORT OFF
end;
October 26, 2011 at 3:54 am
Hi,
You have to use TRY...CATCH construct in order to handle the errors. You have rightly set XACT_ABORT ON before begining the transaction. This way you can make sure that if any of the statement fails, the whole transaction would be rolled back
Below is your modified code to handle the error in CATCH block. Please read the comments as well.
SET XACT_ABORT ON
BEGIN TRY
BEGIN TRANSACTION
--==========================================
-- Insert Customer Info
---=========================================
INSERT INTO dbo.tblCustomer
(
Name
,[Description]
,Title
,IsNewProductNotify
)
VALUES (
@SiteName
,@Description
,@PageTitle
,@IsAutoProductUpdate
) ;
-- Storing tblCustomer identity value in vairable for subsequest insert
SELECT @SiteIdent = IDENT_CURRENT('dbo.tblCustomer') ;
INSERT INTO dbo.tblPage
(
LogoImage
,WelcomeMessage
,SiteId
)
VALUES (
@HeaderBanner
,@WelcomeMessae
,@SiteIdent
) ;
INSERT INTO dbo.tblFeature
(
MetaDescription
,MetaKeyword
,SiteId
)
VALUES (
@MetaKeywords
,@MetaDescription
,@SiteIdent
) ;
COMMIT TRANSACTION
SET XACT_ABORT OFF
END TRY
BEGIN CATCH
DECLARE @ErrorMessage NVARCHAR(4000)
, @ErrorNumber INT
, @ErrorSeverity INT
, @ErrorState INT
, @ErrorLine INT
, @ErrorProcedure NVARCHAR(200) ;
-- Assign variables to error-handling functions that
-- capture information for RAISERROR.
SELECT @ErrorNumber = ERROR_NUMBER()
, @ErrorSeverity = ERROR_SEVERITY()
, @ErrorState = ERROR_STATE()
, @ErrorLine = ERROR_LINE()
, @ErrorProcedure = ISNULL(ERROR_PROCEDURE(), '-') ;
-- Building the message string that will contain original
-- error information. You can add your own custome error message here instead
SELECT @ErrorMessage = N'Error %d, Level %d, State %d, Procedure %s, Line %d, '
+ 'Message: ' + ERROR_MESSAGE() ;
-- Rollback the active transaction
IF @@TRANCOUNT > 0
ROLLBACK TRANSACTION
-- Raise an error: msg_str parameter of RAISERROR will contain
-- the original error information.
RAISERROR
(
@ErrorMessage,
@ErrorSeverity,
1,
@ErrorNumber, -- parameter: original error number.
@ErrorSeverity, -- parameter: original error severity.
@ErrorState, -- parameter: original error state.
@ErrorProcedure, -- parameter: original error procedure name.
@ErrorLine -- parameter: original error line number.
) ;
END CATCH
You can also use XACT_STATE() function in TRY..CATCH block if do/don't want to rollback the whole transaction.
Viewing 2 posts - 1 through 1 (of 1 total)
You must be logged in to reply to this topic. Login to reply