In Sql Server 2008 R2, I am creating a procedure with multiple transactions in it. Try..Catch Block is used for each transaction. I use output parameter to catch the error code and message since it will be caught by the main program. But When there is errors the output parameter are not correct set to the error message and code, what is problem here?
Any help would be really appreciated!
create procedure xxx (@P_Return_Status VARCHAR(1) OUTPUT, @P_Error_Code INT OUTPUT,) AS BEGIN BEGIN TRY BEGIN TRANSACTION TR1 ..... COMMIT TRANSACTIOn TR1 END TRY BEGIN CATCH IF (@@TRANCOUNT > 0) BEGIN ROLLBACK TRANSACTION TR1 END Set @P_Error_Code = Error_Number(); Set @P_Error_Messages = LEFT(ERROR_MESSAGE (), 2000) END CATCH BEGIN TRY BEGIN TRANSACTION TR2 ..... COMMIT TRANSACTIOn TR2 END TRY BEGIN CATCH IF (@@TRANCOUNT > 0) BEGIN ROLLBACK TRANSACTION TR2 END Set @P_Error_Code = Error_Number(); Set @P_Error_Messages = LEFT(ERROR_MESSAGE (), 2000) END CATCH END GO