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?  


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

nolasaintsgalAsked:
Who is Participating?
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

Raja Jegan RSQL Server DBA & Architect, EE Solution GuideCommented:
>> 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..
0

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
ste5anSenior DeveloperCommented:
The first Thing, which comes into mind: why not using a view with instead of insert/update triggers?
0
nolasaintsgalAuthor Commented:
My error handling is working like a charm.  thanks so much!
0
Raja Jegan RSQL Server DBA & Architect, EE Solution GuideCommented:
Welcome, glad to assist!!
0
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Query Syntax

From novice to tech pro — start learning today.