x

Multiple exceptions handling

Hi,

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
 as

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

Begin  try

    begin transaction

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

    commit;

end try

begin catch 
     if @@trancount >0 
         rollback

    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(); 

END CATCH 
more ▼

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

Katie 1 gravatar image

Katie 1
1.4k 132 163 202

(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
         BEGIN TRY
          BEGIN TRANSACTION
              SELECT @@trancount
              SELECT 1/0 --for example to cause an error
         END TRY
         BEGIN CATCH
          ROLLBACK TRANSACTION --b
         END CATCH
END TRY
BEGIN CATCH
    SELECT @@TRANCOUNT
END CATCH
GO 

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

JohnM gravatar image

JohnM
6.8k 1 3 7

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

Katie,

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;
go

create proc dbo.usp_InsertSalesIndividual
(
    @CustomerID int, @ContactID int
)
as

begin;

    set nocount on;

    insert into Sales.Individual (CustomerID, ContactID, ModifiedDate)
    values (@CustomerID, @ContactID, getdate());

    set nocount off;
end;
go

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;

    select 
        @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;
go

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.

Oleg
more ▼

answered Apr 26, 2012 at 06:29 PM

Oleg gravatar image

Oleg
15.9k 2 4 24

(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.

New code box

There's a new way to format code on the site - the red speech bubble logo will automatically format T-SQL for you. The original code box is still there for XML, etc. More details here.

Follow this question

By Email:

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

By RSS:

Answers

Answers and Comments

SQL Server Central

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

Topics:

x1840
x288
x21

asked: Apr 26, 2012 at 05:55 PM

Seen: 931 times

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