SQL Server - TRY/CATCH does not work in certain cases
How can I write guarded code that invokes the CATCH code block in every expected* case (without doing funky junk like wrapping everything in dynamic SQL)?


You can't, unfortunately.

SQL Server MVP Erland Sommarskog has a series of brief* articles on the subject of error handling in SQL Server that starts here: Error and Transaction Handling in SQL Server

The issue you're running into with the Agent job is that it calls an extended stored procedure, which is what raises the error.  In particular, that error comes from master.dbo.xp_sqlagent_notify.

Erland covers that issue here:


  SQL Server still ships with quite a few extended stored procedures, whereof some are documented and others are only intended for the tools that ship with SQL Server. With regards to error handling, all bets are off with these guys. There is no consistent behaviour, and about every XP has its own twist.


I know you mentioned you're not interested bespoke solutions that aren't broadly applicable, but I think it's worth mentioning that, when dealing with Microsoft-shipped stored procedures, one solution to this TRY / CATCH issue is to always capture the return code of the procedure.  If it's non-zero, you can manually raise an error to trigger the CATCH block (thanks Dan Guzman).

* brief, ha!