SQL SERVER – Tips from the SQL Joes 2 Pros Development Series – Structured Error Handling – Day 28 of 35

Answer simple quiz at the end of the blog post and –

Every day one winner from India will get Joes 2 Pros Volume 4.

Every day one winner from United States will get Joes 2 Pros Volume 4.

In everyday life, not everything you plan on doing goes your way. For example, recently I planned to turn left on Rosewood Avenue to head north to my office. To my surprise, the road was blocked because of construction. I still needed to head north, even though the signs told me that turning that direction was impossible. I could have treated the unexpected roadblock in the same way that SQL Server interprets a level 16 severity error. That is to say, I could have halted and simply abandoned my attempt to travel to the office.  In the end, I came up with an alternate plan that was nearly as good.  By using a detour route I was successfully able to avoid this unexpected disruption until the construction zone was cleared. When my “Plan-A” route didn’t work, I tried and found a workable “Plan-B”. Structured Error Handling in SQL Server is similar to the way we approach ‘errors’ in real life. When something does not go exactly as we expected, we adapt and find another way to accomplish our goal.

Structured Error Handling

SQL Server introduced new and improved options for error handling beginning with SQL Server 2005. Prior versions did not include structured error handling.

Anticipating Potential Errors

We’ll begin with an example from the Grant table.  We’ll attempt to set a GrantName value to NULL.  We expect to raise an error, since GrantName is a required field. The GrantName for Grant 001 fails to update to a value of NULL, since that field does not allow nulls.

We can anticipate that updates might fail with an error. We can’t prevent the occasional incorrect input from the client, but best practices require us to control or eliminate the error messaging and provide SQL Server instructions for how to behave when it receives incorrect input.

Let’s next add code to our current example in order to catch these types of input errors.  Structured error handling will help head off errors raised by incorrect inputs received from the client. We expect our users will send correct values and data types into the Grant table most of the time. However, for those few instances where a user attempts an incorrect value, seeing a message from anything besides the application or having to restart the application will not be comfortable experiences for our users. We will add code to deal with these types of errors and ensure that our users receive a message from the application layer (preferably one which gives them a hint they’ve attempted an incorrect value and providing guidance as to what the correct input should be).

The TRY Block

The Try Block is where you place code which you think may raise an error.  A Try Block is a code segment starting with a BEGIN TRY statement and ending with END TRY.  If a statement inside theTry Block and raises an error, then the error gets passed to another part of SQL Server and not to the calling code. The Try Block is aware that there is code which may fail.

The code which is vulnerable to potentially receiving bad input from the client should be enclosed within the Try Block.  Think of your TRY block as Plan-A. This is what you are hoping works on the first try. If Plan-A does not work, we can tell SQL Server to try Plan-B instead of reacting with an error message. Our code which will handle contingency steps (i.e., Plan-B) for bad input will be enclosed in what’s known as a Catch Block.  Only one of these two blocks will run to completion.  Think of the Try Block as “Plan-A” and Catch Block as “Plan-B.”

The CATCH Block

The “Catch Block” serves as a contingency plan for failed code from the Try Block. In other words, if any statement raises a level 11 or higher severity in the Try Block, it will not show the error from the calling code. It will run the code you have set up in the Catch Block.

In the code example below the “Catch Block” never runs, since the Try Block (“Plan-A”) runs fine. In other words, if Plan-A works then there is no need to try Plan-B.

BEGIN TRY
UPDATE [Grant] SET GrantName = '92 Per-cents %% team'
WHERE GrantID = '001'
END TRY
BEGIN CATCH
PRINT 'No Change was made'
END CATCH

Notice the Catch Block never runs, since the Try Block (“Plan-A”) ran OK. Next let’s observe an example where the Try Block will throw an error and not run successfully. Recall our previous example where attempting to set GrantName to NULL threw an error.  In using this same update statement, the Try Block encounters an error and thus will not run.  The Catch Block will run instead.

Note: If you want to setup the sample JProCo database on your system you can watch this video. For this post you will want to run the SQLProgrammingChapter10.0Setup.sql script from Volume 4.

Question 28

You have tables named CurrentProducts and SalesInvoiceHeader. The CurrentProducts table has a foreign key relationship with the SalesInvoiceHeader table on the ProductID column. You are deleting ProductID 77 from the Product table and then trying to insert a sale for Product77 into the SalesInvoiceHeader table.

BEGIN TRY
BEGIN TRANSACTION
DELETE FROM CurrentProducts  WHERE ProductID = 77
BEGIN TRANSACTION
INSERT INTO SalesInvoiceHeader VALUES ( 95894, 77, 2 )
COMMIT TRANSACTION
COMMIT TRANSACTION
END TRY
BEGIN CATCH
ROLLBACK TRANSACTION
PRINT ERROR_MESSAGE()
END CATCH


What will be the outcome when you run this query?

1. 1 The product will be deleted from the CurrentProducts table.
   2) The order details will be inserted into the SalesInvoiceHeader table.
2. 1) The product will be deleted from the CurrentProducts table.
   2) The order details will not be inserted into the SalesInvoiceHeader table.
3. 1) The product will not be deleted from the CurrentProducts table.
   2) The order details will be inserted into the SalesInvoiceHeader table.
4. 1) The product will not be deleted from the CurrentProducts table.
   2) The order details will not be inserted into the SalesInvoiceHeader table.

Rules:

Please leave your answer in comment section below with correct option, explanation and your country of resident.
Every day one winner will be announced from United States.
Every day one winner will be announced from India.
A valid answer must contain country of residence of answerer.
Please check my facebook page for winners name and correct answer.
Every day one winner from India will get Joes 2 Pros Volume 4.
Every day one winner from United States will get Joes 2 Pros Volume 4.
The contest is open till next blog post shows up at which is next day GTM+2.5.

Reference:  Pinal Dave (https://blog.sqlauthority.com)