в обработчик ошибки insert засунь
У меня так
begin transaction

exec(..)
if @@error<>0 goto ErrorHandler
.....
insert into ...
if @@error<>0 goto ErrorHandler
.....
.....
commit transaction
return(0)

ErrorHandler:
rollback transaction
insert into tbl_errors (desc, date) values 'bla-bla-bla',
GetDate()

return (ErrorCode)


Токо тебе нужно в таблицу еще что-то засунуть чтоб
верхняя транзакция могла однозначно определить ID ошибки, можа @@SPID или System_User

Как я понял у тя транзакция начинает верхняя процедура, а остальные токо что-там делают-меняют.

Можно конечно, но не очень гибко ...
Нижние отдельно от верхней использовать корректно не сможешь.
Ваще, любая процедура, меняющая данные более чем в двух
таблицах должна начинаться транзакцией.

Ну а в твоем случае все просто.
Складываешь ошибки в таблицу и анализируй потом их в верхней сколь угодно.

Токо используй правило анализируй ошибки процедур 3 уровня на 2, 2-го на 1. Т.е. не надо в процедуре смотреть ошибки произошедшие в процедуре сгенерированные процедурой, которую вызвала процедура, которуую вызвала процедура ..... etc