Error handling with nested procedures

nolasaintsgal
nolasaintsgal used Ask the Experts™
on
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?  


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

Open in new window

Comment
Watch Question

Do more with

Expert Office
EXPERT OFFICE® is a registered trademark of EXPERTS EXCHANGE®
SQL Server DBA & Architect, EE Solution Guide
Awarded 2009
Distinguished Expert 2018
Commented:
>> I get the feeling that I can't combine in one procedure the separate begin transactions.

Nope, you can club everything together inside a single procedure.
Kindly check the below flow:
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.
Begin the transaction here..
Just use IF EXISTS condition and either INSERT a new record or UPDATE the existing record.

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.
Immediately after the above code, INSERT record to your another table.

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.
Do IF EXISTS check again and either call your ImportMattProfs or else insert it directly within your current procedure code..

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.
Do Rollback or Commit based upon error occurrence here..
ste5anSenior Developer

Commented:
The first Thing, which comes into mind: why not using a view with instead of insert/update triggers?

Author

Commented:
My error handling is working like a charm.  thanks so much!
Raja Jegan RSQL Server DBA & Architect, EE Solution Guide
Awarded 2009
Distinguished Expert 2018

Commented:
Welcome, glad to assist!!

Do more with

Expert Office
Submit tech questions to Ask the Experts™ at any time to receive solutions, advice, and new ideas from leading industry professionals.

Start 7-Day Free Trial