Skip to content

Nesting an Exception

2008 November 3
by Shannon Lowder

Like just about every other technique in Microsoft SQL you can nest your exception handling. The basic template is:

	-- Nested try block
	-- Nested catch block
--catch block

You can do this as many levels deep as you feel necessary. I will say that it is often easier to accomplish this nesting by handing the different steps in separate stored procedures. That way you can handle errors on a more “atomic” or “unit” level.

The template I shared with you before will only handle errors raised by the database engine. If you need to detect for business logic errors, you can take the initiative, and raise your own errors.

RAISERROR ( { msg_id | msg_str | @local_variable }
    { ,severity ,state }
    [ ,argument [ ,...n ] ] )
    [ WITH option [ ,...n ] ]

I’ve mentioned this before, but right now I don’t have an article dedicated to it, so I’ll go over it now. RAISERROR takes three required arguments:

The first argument can be one of three things:

  • The argument can be represented as a constant integer. To start, create a message, assign it a number higher than 50000, and pass that message to Transact-SQL by storing it in the sys.messages library. If you do this, to access the message, you would use the number you specified
  • The argument can be represented as a msg_str object. In this case, the argument is the message you want to produce (or display) if an error occurs. The argument is created and formatted like the printf() function of the C language
  • The first argument can be a string-based locally declared variable. It is then initialized and formatted as done for the msg_str option
  • The second argument is a number that represents the severity level of the error. You specify this number as you see fit, knowing that you will manage it later as you see fit. The number specified for this argument should be between 0 and 18. If you are a member of the sysadmin group, you can specify a number higher than that. If you use a number between 20 and 25, this is considered very high (or a dangerous error) and can close the connection to the database
  • The third argument is a number that represents the error state. For this argument, you can specify any number between 1 and 127. If you are creating different exceptions sections, you should provide a unique state number for each
  • That’s it for nesting error handling, and raising your own errors. If you have any questions, let me know. I’m here to help!

    No comments yet

    Leave a Reply

    Note: You can use basic XHTML in your comments. Your email address will never be published.

    Subscribe to this comment feed via RSS