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

I am really not one hundred percent sure about what you are  doing is correct but there are few problems: 

What if both the transaction block fails at the same time , you won't be able to capture first one.

IF both really happens to be different transactions , then you can separate both the logic in two different procs and use a generic proc to implement error handling:

Please read these :

https://msdn.microsoft.com/en-IN/library/ms175976.aspx?f=255&MSPPError=-2147217396

http://www.sommarskog.se/error_handling/Part1.html

How are you calling the procedure xxx?  If you want the procedure to return a value in an output parameter, you must specify OUTPUT both when you create the procedure and when you call it.  That is, your call should look like

EXEC xxx @My_Return_Status OUTPUT, @My_Error_Code OUTPUT

not

EXEC xxx @My_Return_Status, @My_Error_Code

Tom