Here's a sample of using transact code to run multiple SPs. The SPs in this code are performing update commands on my database so I've got the transact code setup to rollback any transactions if a part of the process fails. It also catches the error and
stores it so I can keep track of any failures. This is probably a little more than what you're needing for what you're trying to accomplish:
USE [MyDatabase]
GO
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
-- =============================================
-- Author: Will B
-- Create date: 01/30/2009
-- Description: Transact Proc To Update Rental Profit Report
-- =============================================
ALTER PROCEDURE [dbo].[usp_RentalProfitUpdate]
(
@DateNum1 varchar(8),
@DateNum2 varchar(8),
@DateShort1 varchar(10),
@DateShort2 varchar(10),
@mrun varchar(2),
@yrun varchar(4),
@PeriodYear varchar(8)
)
As
Begin
Begin Try
Begin Tran
Exec usp_RFProfitUpdate1 @DateNum1,@DateShort2,@mrun,@yrun
Exec usp_RFProfitUPdate2 @DateNum1,@mrun,@yrun
Exec usp_RFProfitUPdate7 @DateNum1,@mrun,@yrun
Exec usp_RFProfitUpdate3 @DateShort1,@DateShort2,@mrun,@yrun
Exec usp_RFProfitUpdate4 @mrun,@yrun
Exec usp_RFProfitUpdate5 @PeriodYear,@mrun,@yrun
Exec usp_RFProfitUpdate6 @mrun,@yrun
Commit Tran
End Try
Begin Catch
If @@Trancount > 0 Rollback
Insert Into RentalProfitabilityTblErr(DateOfError,ErrorNum,ErrorProc)
Values (GetDate(),Error_Number(),Error_Procedure())
End Catch
End
Loganix77
Participant
1351 Points
412 Posts
Re: What is the better way to call multiple Stored Procedures
Apr 30, 2012 07:44 PM|LINK
Here's a sample of using transact code to run multiple SPs. The SPs in this code are performing update commands on my database so I've got the transact code setup to rollback any transactions if a part of the process fails. It also catches the error and stores it so I can keep track of any failures. This is probably a little more than what you're needing for what you're trying to accomplish: