Geeks With Blogs
Caffeinated Coder A Grande, Triple Shot, Non-Fat Core Dump by Russell Ball

I knew that SQL Server 2005 had structured exception handling, but for some reason I assumed that you could only use it within CLR sprocs. Since I still haven't actually heard of anyone using CLR sprocs for anything other than demos or sample projects (at least not without being pummeled by hoards of angry DBA's), I mostly ignored this new feature until now.

Today I worked on a sproc that involved over a dozen DML statements (it was a utility sproc to handle security and setup steps for developers as part of our build process, so there is no need to chastise me about it being bad design to have so much logic in the database). The thought of copy-pasting dozens of tedious procedural error handling code snippets was so distasteful to me that I decided to do a quick verigoogle. Verigoogling is my freshly coined word for challenging one of my assumptions through google. I was delighted to discover that this new Try/Catch syntax is available to the masses to be used in plain vanilla sprocs. Instead of checking the @@error variable after each statement, we can now be lazy..er..I mean concise and consolidate all the error handling code into the catch block like we're used to doing in good ole' OOP code.

For those of you like me who haven't been taking advantage of this new feature yet, here is some sample code that demonstrates the how easy it is to use. As for the rest of you, please restrain yourself for making fun of me and go verigoogle one of your own assumptions.

BEGIN TRY

      BEGIN TRANSACTION

          DELETE FROM table1

          DELETE FROM table2

      COMMIT

END TRY

BEGIN CATCH

    IF @@TRANCOUNT > 0
           ROLLBACK

     DECLARE @ErrMsg nvarchar(4000), @ErrSeverity int
     SELECT @ErrMsg = ERROR_MESSAGE(),
     @ErrSeverity = ERROR_SEVERITY()

     RAISERROR(@ErrMsg, @ErrSeverity, 1)

END CATCH

 

Posted on Monday, September 17, 2007 11:36 PM Technical How-To's | Back to top


Comments on this post: Don't Forget to Verigoogle

No comments posted yet.
Your comment:
 (will show your gravatar)


Copyright © Russell Ball | Powered by: GeeksWithBlogs.net