question

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!!

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