troubleshooting Question

Error handling with nested procedures

Avatar of nolasaintsgal
nolasaintsgal asked on
Microsoft SQL ServerSQL
4 Comments1 Solution94 ViewsLast Modified:
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
Join the community to see this answer!
Join our exclusive community to see this answer & millions of others.
Unlock 1 Answer and 4 Comments.
Join the Community
Learn from the best

Network and collaborate with thousands of CTOs, CISOs, and IT Pros rooting for you and your success.

Andrew Hancock - VMware vExpert
See if this solution works for you by signing up for a 7 day free trial.
Unlock 1 Answer and 4 Comments.
Try for 7 days

”The time we save is the biggest benefit of E-E to our team. What could take multiple guys 2 hours or more each to find is accessed in around 15 minutes on Experts Exchange.

-Mike Kapnisakis, Warner Bros