Thanks Roger. Regarding "Errors Unaffected by a Try/Catch Construct" the issues are mitigate-able (imo (all of what follows)) if handled properly (knock on wood). I must be biased tho because I don't see an alternative to Try/Catch functionality and my projects rely on it extensively, full disclosure. My disconnect must be because the only clients directly connecting to the database(s) I deal with are a bunch of .NET APIs. No web sites or anything else (besides a few internal tools) connect directly to the database(s). What you say about always returning an error in .NET is true. Maybe the client connects directly to the database and so we just are describing the same endpoint differently. Or it's the same! It's hard to articulate because everything depends on so many things. From the Docs [which I switched from bullet points to #s] the non-catch-able errors listed are:
TRY...CATCH constructs do not trap the following conditions:
- Warnings or informational messages that have a severity of 10 or lower.
- Errors that have a severity of 20 or higher that stop the SQL Server Database Engine task processing for the session. If an error occurs that has severity of 20 or higher and the database connection is not disrupted, TRY...CATCH will handle the error.
- Attentions, such as client-interrupt requests or broken client connections.
- When the session is ended by a system administrator by using the KILL statement.
#1 is just ignored which is ok because the db likely delivered the correct result.
#2 the database engine is FUBAR. In this case the APIs have health checks which would email me (knock on wood).
#3 this happens all the time between the APIs and the clients. Things happen especially with phone apps. Between .NET and SQL Server (which is an isolated connection) this is hopefully very infrequent. Things happen tho. Hopefully health checks catch this too. The apis continually ping the db engine.
#4 it would be me who was doing the killing 🙂
Then the second set of things it TRY/CATCH doesn't catch:
The following types of errors are not handled by a CATCH block when they occur at the same level of execution as the TRY...CATCH construct:
- Compile errors, such as syntax errors, that prevent a batch from running.
- Errors that occur during statement-level recompilation, such as object name resolution errors that occur after compilation because of deferred name resolution.
- Object name resolution errors
The code I posted checked for the existence of the table in test_proc_b and interrupts execution by throwing an error prior to the required resolution of the object (table_t or whatever). My working example tho didn't work because the code that checked @sys_table_name was commented out. If that code were not commented out then it would've worked I think.
Aus dem Paradies, das Cantor uns geschaffen, soll uns niemand vertreiben können