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

SQL SERVER - Tips from the SQL Joes 2 Pros Development Series - Structured Error Handling - Day 28 of 35 joes2pros4 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.

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

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.”

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

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.

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

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)

Joes 2 Pros, SQL Scripts
Previous Post
SQL SERVER – Tips from the SQL Joes 2 Pros Development Series – SQL Server Error Messages – Day 27 of 35
Next Post
SQL SERVER – Tips from the SQL Joes 2 Pros Development Series – What is XML? – Day 29 of 35

Related Posts

47 Comments. Leave new

  • Rajesh Mohanrangan
    August 28, 2011 7:35 pm

    Ans:Option 4

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

    Regards
    Rajesh
    From india

    Reply
  • Correct Answer : Option 4

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

    Reply
  • Correct Answer : Option 4

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

    Ahmedabad,India

    Reply
  • Nikhil Mahajan
    August 28, 2011 10:04 pm

    the correct answer is option 4 i.e.

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

    India

    Reply
  • Answer:4 is correct
    1) The product will not be deleted from the CurrentProducts table.
    2) The order details will not be inserted into the SalesInvoiceHeader table.
    Krishan kumar mishra

    Reply
  • Rajneesh Verma
    August 28, 2011 11:19 pm

    The correct option is option 4

    Thanks.
    Rajneesh Verma
    (INDIA)

    Reply
  • Answer is #4.

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

    Before commit we will get error and hence transaction will be rolled back.

    Chetan – USA

    Reply
  • optio4 will be the result of the given SQL statement.

    Country: United States

    Reply
  • Hi Pinal,

    Challenge:
    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.

    Correct Answer:
    The correct choice is #4:

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

    Explanation:
    The insertion of the data into the SalesInvoiceHeader table will fail because the previous transaction deleted ProductID 77 from the table. Since the code above shows a nested transaction for the insertion of the SalesInvoiceHeader data, the error in that transaction will initiate the code in the Catch section, which will ROLLBACK both transactions, and then run the print any error messages that are generated from the ERROR_MESSAGE( ) function.

    Country:
    United States

    Thanks for the knowledge!

    Regards,

    Bill Pepping

    Reply
  • Basavaraj Biradar
    August 29, 2011 12:23 am

    Correct Answer is option 4

    Thanks,
    Basavaraj
    India

    Reply
  • Question 28: Answer is option 4.

    First of all, you cannot insert a foreign key when it’s primary key does not exist. This violates integrity constraints.
    Second, since there is an exception, the commit does not occur.

    Thanks
    Vijay from USA

    Reply
  • Correct Option is 4

    I’ from INDIA

    Reply
  • Answer is Option # 4

    Reply
  • Correct Option 4.
    Because If error occure in inner transaction then outer transactions would also be rolled back.

    Azhar Iqbal
    From Lahore Pakistan.

    Reply
  • The Correct option is
    #4.

    Country : India

    Reply
  • Answer is option 4

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

    Country : India

    Reply
  • The correct answer is :
    4.1) The product will not be deleted from the CurrentProducts table.
    2) The order details will not be inserted into the SalesInvoiceHeader table.

    Sudeepta,
    India.

    Reply
  • Kulshresth Bhardwaj
    August 29, 2011 12:28 pm

    Kulshresth Bhardwaj
    Correct Option is 4

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

    Country : India

    The product will not be deleted from the CurrentProducts table as it will violate the foreign key constraint . As ProductID is the foreign key in SalesInvoiceHeader Table and it references the ProductID of the CurrentProduct table where it acts as a primary key. and we are trying to delete the value of a column ProductID (CurrentProducts ) of which ProductID (SalesInvoiceHeader table) depends.

    Reply
  • correct answer is option 4
    India

    Reply
  • sachin kulshreshtha
    August 29, 2011 5:00 pm

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

    Noida, INDIA

    Reply

Leave a Reply