Nested stored procedures and transactions

in tsql, many times i write a Stored procedure & then i nest few other sp's within that procedure. my goal is to rollback the entire transaction even if one sp fails. here is the code for that.


USE [ajx_web_cms]
GO
/****** Object:  StoredProcedure [dbo].[Amazon_Reports_Processing]    Script Date: 03/14/2014 02:38:31 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO

ALTER Proc [dbo].[Amazon_Reports_Processing]
(
@TableName varchar(100)
)
As

SET XACT_ABORT, NOCOUNT ON

Begin TRY
        BEGIN TRANSACTION -- any log/nested procs add below
       
--****************************************************************************
-- AMAZON Order Report
--****************************************************************************
IF @TableName = 'Amz_GetOrders_Temp' EXEc sp_Am_GetOrders
IF @TableName = 'Amz_Unshipped_Temp' EXEc sp_Am_UnShipped
-- LOG IT
insert into Amazon_Report_Logs(msgType,msgTxt) Values('SP Amazon_Reports_Processing', @TableName)
        COMMIT TRANSACTION
        
End Try
Begin Catch
IF XACT_STATE() <> 0 
        ROLLBACK TRANSACTION
        
Declare @ErrorMrssage varchar(4000)
Set @ErrorMrssage = Replace(ERROR_MESSAGE(), '''', '')
insert into Amazon_Report_Logs(msgType,msgTxt) Values('ERROR: SP Amazon_Reports_Processing Failed To Process. ' + @TableName , @ErrorMrssage)
End Catch

Posted by:

No comments posted.

Name :  

Email :  

Comment Below