Error handling is a little understood aspect of T-SQL programming. In this session, Greg will explore the error handling options available in T-SQL and show how the structured exception handling provided by SQL Server 2005 enhances these options. He will also show some of the limitations of these new capabilities and provide recommendations on the scenarios in which they are useful.
Greg is an internationally recognised consultant, developer and trainer. He has been working in development since 1978, holds a PhD in Computer Science and MC*.* from Microsoft. Greg is the lead SQL Server consultant with Readify, a SQL Server MVP and one of only two Microsoft MSDN Regional Directors for Australia. Greg also hosts the SQL Down Under podcast (www.sqldownunder.com), organises the SQL Down Under Code Camp and co-organises CodeCampOz.
Joke from last night’s Camp Fire Dinner: Greg was voted "Most likely to replace (Microsoft CEO) Steve Balmer."
Things I’ve learned from this session:
Errors are group by severity: 0 is INFORMATIONAL (e.g. PRINT)
If I nest transactions, commit will commit the inner transaction(s) and decrement the @@TRANSCOUNT by one.
If, however, I rollback, then the outer transaction will rollback, resetting @@TRANSCOUNT to zero.
Currently, if you handle errors in SQL, you cannot throw errors back to the calling proc.
Reference: A Developer’s Guide for SQL Server 2005 by Bob Beauchemin.