The bad news is this can be done with SQL only for some error but cannot be done for all types of errors.

When an error is encountered, a termination occurs that could include the statement, the procedure/trigger, the batch or the connection. One can only code for those errors that terminate the statement and not for any of the other terminate scopes.

See Erland Sommarskog's papers on this subject at

http://www.sommarskog.se/error-handling-I.html

http://www.sommarskog.se/error-handling-II.html