nolasaintsgal
asked on
Error handling with nested procedures
I have a procedure that I'm trying to incorporate error handling into but am getting error messages regarding multiple begin transactions. Below is a more simplified version of my procedure.
1) I want to either update an existing record's open date or create a new record if it doesn't exist in a table called Matters.
2) After the matter data is committed to the database I need to populate another table with the people who are assigned to work on the matter.
3) So if the matter data is committed without error then the procedure calls Procedure ImportMattProfs and passes it information to create a related record in the MattProf table.
4) If an error occurs when the data is being inserted into the MattProf table then I want to roll back all transactions, including the insert of the record into the Matter table.
I get the feeling that I can't combine in one procedure the separate begin transactions. Do I have to just call the ImportMattProf procedure and implement all the error handling inside ImportMattProf? If so, where do I put the call to Exec ImportMattProfs because I can't insert into the MattProf table until the data in the Matters table is committed?
1) I want to either update an existing record's open date or create a new record if it doesn't exist in a table called Matters.
2) After the matter data is committed to the database I need to populate another table with the people who are assigned to work on the matter.
3) So if the matter data is committed without error then the procedure calls Procedure ImportMattProfs and passes it information to create a related record in the MattProf table.
4) If an error occurs when the data is being inserted into the MattProf table then I want to roll back all transactions, including the insert of the record into the Matter table.
I get the feeling that I can't combine in one procedure the separate begin transactions. Do I have to just call the ImportMattProf procedure and implement all the error handling inside ImportMattProf? If so, where do I put the call to Exec ImportMattProfs because I can't insert into the MattProf table until the data in the Matters table is committed?
CREATE PROCEDURE [dbo].[ImportMatters]
AS
SET XACT_ABORT, NOCOUNT ON
DECLARE @ID VARCHAR(25) = NULL
DECLARE @OpenedDate DATETIME = NULL
DECLARE @StartTranCount INT
/* Create the cursor for the list of matters to be inserted or updated */
DECLARE MatterList cursor for
SELECT
LTRIM(RTRIM(a.ClientCode)) + '-' + LTRIM(RTRIM(a.MatterCode)) AS ID,
DateOpened,
FROM import_matter a
OPEN MatterList
FETCH NEXT FROM MatterList INTO
@ID,
@OpenedDate,
WHILE @@FETCH_STATUS = 0
BEGIN TRY
SELECT @StartTranCount = @@TRANCOUNT
If @StartTranCount = 0
BEGIN TRANSACTION IMPORTMATTERS
/* Check to see if the matter already exists */
IF EXISTS (SELECT MatterID FROM Matters WHERE MatterID = @ID)
BEGIN
<DO SOME UPDATES HERE>
/* Insert record into MattsTracking indicating successful update */
INSERT INTO MattsTracking (TrackingTime, TrackDesc, Matters, Matterid, Success)
VALUES (GETDATE(),'Update',@Matters,RTRIM(@ID),'Y')
END
ELSE -- Matter doesn't exist, create a new matter
BEGIN
<DO AN INSERT HERE>
/* Insert record in MattsTracking indicating successful insert */
INSERT INTO MattsTracking (TrackingTime, TrackDesc, Matters, Matterid, Success)
VALUES (GETDATE(),'Insert Matter',@Matters,RTRIM(@ID),'N')
END
IF @StartTranCount=0
COMMIT TRANSACTION MATTERS
END TRY
BEGIN CATCH
IF XACT_STATE() <> 0 AND @StartTranCount = 0
BEGIN
/* Insert error record tracking info into MattsTracking and rollback all transactions */
INSERT INTO MattsTracking (TrackingTime, TrackDesc, Matters, Matterid, Success, ErrorMessage)
VALUES (GETDATE(),'Insert',@Matters,RTRIM(@ID),'N', ERROR_MESSAGE())
ROLLBACK TRANSACTION IMPORTMATTERS
END
END CATCH
/* Insert the assigned professionals relate records - calls procedure that inserts records into MattPpof table */
BEGIN TRY
SELECT @StartTranCount = @@TRANCOUNT
IF @StartTranCount=0
BEGIN TRANSACTION MATTPROF
--set some variables and then call procedures to populate MattProf
EXEC ImportMattProfs @Matters, @ProfID, @AssignedType
IF @StartTranCount = 0
COMMIT TRANSACTION MATTPROF
END TRY
BEGIN CATCH
IF XACT_STATE() <> 0 AND @StartTranCount = 0
BEGIN
/* Insert error record tracking info into SC_MattsTracking and rollback all transactions */
INSERT INTO MattsTracking (TrackingTime, TrackDesc, Matters, Matterid, Success, ErrorMessage)
VALUES (GETDATE(),'MattProf',@Matters,RTRIM(@ID),'N', ERROR_MESSAGE())
ROLLBACK TRANSACTION IMPORT_MATTPROF
END
END CATCH
END
FETCH NEXT FROM MatterList INTO
@ID,
@OpenedDate
END
CLOSE MatterList
DEALLOCATE MatterList
SET NOCOUNT OFF
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
The first Thing, which comes into mind: why not using a view with instead of insert/update triggers?
ASKER
My error handling is working like a charm. thanks so much!
Welcome, glad to assist!!