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?
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:
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!!
answered Apr 26 '12 at 06:15 PM
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:
Here is the block which is based on yours, and it is bound to fail because of the foreign key violations:
The above produces all the information you need (with results to text option):
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
answered Apr 26 '12 at 06:29 PM