Nesting an Exception

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!

    By Shannon Lowder

    Shannon Lowder is the Database Engineer you've been looking for! Look no further for expertise in: Business Analysis to gather the business requirements for the database; Database Architecting to design the logical design of the database; Database Development to actually build the objects needed by the business logic; finally, Database Administration to keep the database running in top form, and making sure there is a disaster recovery plan.

    Leave a comment

    Your email address will not be published. Required fields are marked *