How can SQL code behave differently in or outside procedure and about poor error handling

During refactoring of very old code, I found a ancient/ugly and in the end even totally wrong error handling script. Interesting is that the developer probably tested it outside of procedure and it did "kind a work" for him, ... :)

I'll add details later (or much later).