Link to home
Start Free TrialLog in
Avatar of nolasaintsgal
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?  


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

ASKER CERTIFIED SOLUTION
Avatar of Raja Jegan R
Raja Jegan R
Flag of India image

Link to home
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
Start Free Trial
The first Thing, which comes into mind: why not using a view with instead of insert/update triggers?
Avatar of nolasaintsgal
nolasaintsgal

ASKER

My error handling is working like a charm.  thanks so much!
Welcome, glad to assist!!