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