Nesting an Exception

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

BEGIN TRY
    BEGIN TRY
	-- Nested try block
    END TRY
    BEGIN CATCH
	-- Nested catch block
    END CATCH
END TRY
BEGIN CATCH
--catch block
END CATCH

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!