SQL Server Stored Procedure Error Handling -

i have stored procedure runs automatically every morning in sql server 2008 r2, part of stored procedure involves executing other stored procedures. format can summarised thus:

begin try   -- various sql commands   execute storedprocedure1   execute storedprocedure2   -- etc end try  begin catch   --this logs error table   execute errortrappingprocedure end catch 

storedprocedure1 , storedprocedure2 truncate table , select table. along lines of:

begin try   truncate table1    insert table1 (a, b, c)   select a, b, c maintable end try  begin catch   execute errortrappingprocedure end catch 

the error trapping procedure contains this:

insert      [internal].dbo.error_trapping     (         [error_number],         [error_severity],         [error_state],         [error_procedure],         [error_line],         [error_message],                 [error_datetime]     )     (     select          error_number(),         error_severity(),         error_state(),         error_procedure(),         error_line(),         error_message(),         getdate()     ) 

99% of time works, find storedprocedure1 hasn't completed, table1 being part populated. no errors logged in our error table. i've tested error trapping procedure , work.

when later run storedprocedure1 manually completes fine. no data in source table have changed point it's not problem data, else has happened in instant has caused procedure fail. there better way me log errors here, or somewhere else within database can try , find out why failing?

there type of errors try..catch block not handle them, here more information https://technet.microsoft.com/en-us/library/ms179296(v=sql.105).aspx . such errors should handle them in application. think might have transaction management problem in application too.