Multiple exceptions handling


currently, i have a master procedure, which executes when no runtime errors are found and rolls back when there is an error. The requirement, apart from other errors, if the error is a foriegn key relationship error, then, istead of just a message that it conflicted with the forign key need on which exact key is the proc failing and print it out. in the try i can write the if exists code to see, but can i handle the catch block, can i also have multiple catch blocks for one try block? If that is possible, how do i handle the transaction, can i roll it back at the end of the 2nd catch block?

 ALTER procedure usp_masterproc

  DECLARE @ErrMsg nvarchar(4000),
  @ErrSeverity int  ,
  @errorNum int

 Begin  try
     begin transaction

     exec sp1;
     exec sp2;
     exec sp3;
     exec sp4;
     exec sp5;
     exec sp6

 end try

 begin catch 
      if @@trancount >0 

     RAISERROR(@ErrMsg, @ErrSeverity, 16)  
     SELECT @ErrMsg = ERROR_MESSAGE(),    
     @ErrSeverity = ERROR_SEVERITY(),
     @errorNum = ERROR_NUMBER()

     PRINT 'Error Number: ' + CAST(ERROR_NUMBER() AS VARCHAR); 
     PRINT 'Error Message: ' + ERROR_MESSAGE(); 
     PRINT 'Error Severity: ' + CAST(ERROR_SEVERITY() AS VARCHAR); 
     PRINT 'Error State: ' + CAST(ERROR_STATE() AS VARCHAR); 
     PRINT 'Error Line: ' + CAST(ERROR_LINE() AS VARCHAR); 
     PRINT 'Error Proc: ' + ERROR_PROCEDURE(); 

more ▼

asked Apr 26, 2012 at 05:55 PM in Default

avatar image

Katie 1
1.4k 132 164 205

(comments are locked)
10|1200 characters needed characters left

2 answers: sort voted first

I don't believe that you can have multiple CATCH blocks for a single TRY block. You can however, nest the TRY/CATCH blocks to help give you the control that I think that you are looking for.

Here's a rough (I'm sure someone has a better one out there) example along w/ a nested transactions:

             BEGIN TRY
                 BEGIN TRANSACTION
                     SELECT @@trancount
                     SELECT 1/0 --for example to cause an error
             END TRY
             BEGIN CATCH
                 ROLLBACK TRANSACTION --b
             END CATCH

Keep in mind that if the nested ROLLBACK is hit, it will rollback all of the active transactions, regardless of which level the error occurred.

Does this help? I'm not quite sure if it is exactly what you need, but I thought that I would at least throw it out there.

Hope it helps!!

more ▼

answered Apr 26, 2012 at 06:15 PM

avatar image

14.5k 3 7 15

(comments are locked)
10|1200 characters needed characters left


You have the line in your CATCH block which is in the wrong place. After the transaction is rolled back you immediately call RAISERROR passing parameters to it which have not yet been set. Then you try to set them but it is too late, your proc is already out of the CATCH block thus making your error unhandled. Is this is not what you need you can simply comment out the line to see whatever you wanted to be printed. For example, suppose I have a proc like this:

 use AdventureWorks;
 create proc dbo.usp_InsertSalesIndividual
     @CustomerID int, @ContactID int
     set nocount on;
     insert into Sales.Individual (CustomerID, ContactID, ModifiedDate)
     values (@CustomerID, @ContactID, getdate());
     set nocount off;

Here is the block which is based on yours, and it is bound to fail because of the foreign key violations:

 declare @ErrMsg nvarchar(4000);
 declare @ErrSeverity int;
 declare @errorNum int;
 begin try
     begin tran;
     -- this cannot work because there is 
     -- no CustomerID = -1 in the parent table
     exec dbo.usp_InsertSalesIndividual -1, -1;
     commit tran;
 end try
 begin catch
     if @@trancount > 0 rollback tran;
         @ErrMsg = error_message(),    
         @ErrSeverity = error_severity(),
         @errorNum = error_number();
     print 'Error Number: ' + cast(@errorNum as varchar); 
     print 'Error Message: ' + @ErrMsg; 
     print 'Error Severity: ' + cast(@ErrSeverity as varchar); 
     print 'Error State: ' + cast(error_state() as varchar); 
     print 'Error Line: ' + cast(error_line() as varchar); 
     print 'Error Proc: ' + error_procedure();
     if @errorNum = 547
         print 'Yep, this error is caused by foreign key violation :) ';
 end catch;

The above produces all the information you need (with results to text option):

 Error Number: 547
 Error Message: The INSERT statement conflicted with the FOREIGN KEY 
 constraint "FK_Individual_Customer_CustomerID". The conflict occurred in
 database "AdventureWorks", table "Sales.Customer", column 'CustomerID'.
 Error Severity: 16
 Error State: 0
 Error Line: 11
 Error Proc: usp_InsertSalesIndividual
 'Yep, this error is caused by foreign key violation :) 

It gives you the procedure name, the name of the foreign key and also the name of the offending column. From what I understand, there is only one CATCH per TRY, but this does not prevent you from having multiple (not nested) try/catch blocks if you need them. In this case if you have the outer transaction and then inner transaction per block, nothing will be committed if any of the inner transactions roll back. You will still get the error information printed with all the details you need.


more ▼

answered Apr 26, 2012 at 06:29 PM

avatar image

20.6k 3 7 29

(comments are locked)
10|1200 characters needed characters left
Your answer
toggle preview:

Up to 2 attachments (including images) can be used with a maximum of 524.3 kB each and 1.0 MB total.

Follow this question

By Email:

Once you sign in you will be able to subscribe for any updates here



Answers and Comments

SQL Server Central

Need long-form SQL discussion? SQLserverCentral.com is the place.



asked: Apr 26, 2012 at 05:55 PM

Seen: 1215 times

Last Updated: Apr 26, 2012 at 06:29 PM

Copyright 2018 Redgate Software. Privacy Policy