Sql server prodecure not work correctly

Transact-SQL
1

Hello Dear;
I have a big problem with sql server(I use sql server version 2008 R2 with my application).
for example i have 3 table.
1- Tmp_Table
2- T_Dest1
3- T_Dest2

at the first my data save on Tmp_Table from Application then on Procedure Call from application
I have 3 Procedure aon database and from application call Main procedure.

Procedure PTwo()
As
Begin
  --Do Heavy Work
  insert into T_Dest1 Select * from Tmp_Table
  if @@ERROR > 0 Goto Rb;

  if @@ERROR > 0 
  Begin
    RB: 
    Return (-1);
  End Else Begin
    Return (1);
  End
End;

Procedure PThree()
As
Begin
  --Do Heavy Work
  insert into T_Dest2 Select * from Tmp_Table
  if @@ERROR > 0 Goto Rb;

  if @@ERROR > 0 
  Begin
    RB: 
    Return (-1);
  End Else Begin
    Return (1);
  End
End;



Procedure Main()
As
Begin
  Declare @Res_PTwo int, @Res_PThree int;

 Begin Tran T_Main;

  --Do Heavy work;
  Exec @Res_PTwo = PTwo();
  if @Res_PTwo = -1 Goto RB;

  --Do Heavy Work;
  Exec @Res_PTHree = PThree();
  if @Res_PThree = -1 Goto RB;

  Delete from Tmp_table

   if @@ERROR > 0
  Begin
    RB:
    Rollback Tran T_Main;
    Return (-1);
  End Else
  Begin
    Commit Tran T_Main;
    Return (1);
  End;
 End;
End;

for very long time and many customer all thing is good but in few time (for example 2 or three time in week or day)
Main Proc dont work correctly
and data from Tmp_Table copy to T_Dest1 correctly but don't copy in T_Dest2 but Main Proc return 1 that means
all thing is OK.

realy i confused and i don't know what is incorrect in my procedure and why my SQL Server dont work correctly.

is there any tools to find this mistake?

can i find when data to be lose from Tmp_Table?

thanks for all. and excuse me for my bad language.
rea

2

u may try sql server logs for the first steps

3

how i can use sql logs

4

try to find any error in the log, because you didn't provide us any error thus we will not know whats going wrong.

5

You would be best to use exception handling.

At a minimum:

SET XACT_ABORT, NOCOUNT ON

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