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 http://blog.sqlauthority.com which is next day GTM+2.5.

Reference:  Pinal Dave (http://blog.SQLAuthority.com)

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

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

    This is a very interesting and a bit complex question and in addition to knowing TRY/CATCH we need to know the behavior of the nested transactions. When we rollback nested transactions all transactions are rolled back. I knew it, but just in case I experimented with this code to be absolutely sure in my answer.

    Thanks for this interesting article and great question

    I am from USA

    Like

  2. Correct answer is No. 2: (a) The product will be deleted from the CurrentProducts table, and (b) The order details will not be inserted into the SalesInvoiceHeader table. This is because both tables have a foreign key relationship. We can delete a record from CurrentProducts table, but after deletion we can’t insert that deleted record into SalesInvoiceHeader, where products are referred by using a foreign key, which is precisely the one we just have deleted.
    Rene Castro
    El Salvador

    Like

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

    Explanation : The product table having a FK relationship with SalesInvoiceHeader table.
    It means SalesInvoiceHeader –> ProductId is a PK.
    In first transaction, it will delete the row from product table.
    In second transaction, When we are trying to insert dups in SalesInvoiceHeader, it will blow up and come to the catch block and rollback the deleted record from Product table.

    USA

    Like

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

    Lalit
    India

    Like

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

    After the product is deleted from CurrentProducts the entry order details in salesinvoiceheader will throw and error as this cannot be inserted. Because of the thrown error the products deleted will go through Rollback of its transaction. hence Product will also not be deleted.

    Leo Pius
    USA

    Like

  6. Correct answer is 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.

    Explanation:
    Since ProductId has FK relationship with SalesInvoiceHeader before deletion of record in CurrentProducts table we need to delete it in SalesInvoiceHeader.
    So option 4.(1) is correct.
    Because here exception will come on line no 3 in T-SQL code
    DELETE FROM CurrentProducts WHERE ProductID = 77
    So control will directly move to CATCH block,no chance of insertion in SalesInvoiceHeader table.
    So option 4.(2) is correct.

    New Delhi
    India

    Like

  7. Hi Sir,

    The correct option is 4th option

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

    Step 1 : It will delete the ProductID = 77 from the CurrentProducts table.
    step 2 : While tryin to insert the order with ProductId 77 in the SalesInvoiceHeader table the statement will fail as there is foreign key relationship with CurrentProducts table.

    Step 3 : Then the catch block will execute which rolls back the transaction hence the deletion of ProductID = 77 will be rolled back.

    Therefore the Product will not be deleted from the CurrentProducts table and the order details will not be inserted into the salesInvoiceHeader table.

    Nice question sir.
    Thanks for the knowledge.

    P.Anish Shenoy,
    INDIA, Bangalore, Karnataka.

    Like

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

    Chennai, TamilNadu, India

    Like

  9. Correct option 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.

    The CurrentProducts table has a foreign key relationship with the SalesInvoiceHeader table on the ProductID column. The product will not be deleted because of the foreign key constraint and since it is not deleted the insert statement will not take place.

    (Sale, Nigeria)

    Like

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

    The reason is quite simple, actually and involves knowledge of two concepts: TRY…CATCH (obviously) and “nested” transactions.
    There are no nested transactions without SAVEPOINT, and therefore the ROLLBACK rolls back all open transactions all the way to the outermost transaction.

    Country of residence: India

    Like

  11. ans 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.
    first it will delete the product from the product table .Then it will try to insert the value in the SalesInvoiceHeader table at that time one sql server throw an error so it will go to catch block and though no transaction committed till so it will roll back transaction .So Delete operation will rollback .
    So the result will be neither delete from currentProducts nor insert in the SalesInvoiceHeader .
    so only option 4 is correct.

    Partha,
    India

    Like

  12. Correct answer option no. 4

    explanation : first delete query throw an exception because of fk relation between two tables.

    and transaction rollback.

    shekhar gurav.
    country : India

    Like

  13. The 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.

    The product will not be deleted from the CurrentProducts table because there is a Foreign Key Constraint between it and the SalesInvoiceHeader table. The record would need to be deleted from the SalesInvoiceHeader table first and then from the CurrentProducts table. SInce it is not deleted from SalesInvoiceHeader first, the statement will fail and move into the CATCH block. Because it moved into the catch block right away, the Insert code is never called and thus the order details do not get inserted into SalesInvoiceHeader.

    Deb – USA

    Like

  14. The correct answer for the above question is 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.

    dilip kumar jena
    country india

    Like

  15. Pingback: SQL SERVER – Win a Book a Day – Contest Rules – Day 0 of 35 Journey to SQLAuthority

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

    Like

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

    Like

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

    Like

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

    Like

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

    Like

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

    Like

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

    Azhar Iqbal
    From Lahore Pakistan.

    Like

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

    Like

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

    Like

  25. The correct option 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.

    Because the TRY statement would generate the error, the CATCH would be executed instead. The code from the TRY block would never run. 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.

    Country of residence: USA

    Like

  26. Pingback: SQL SERVER – Programming and Development – Book Available for SQL Server Certification Journey to SQLAuthority

  27. Option 4 as delete statement tends to catch block where rollback will undo all actions done in transaction and too in nested transactions.

    This is Ram from India

    Like

  28. Pingback: SQL SERVER – Expanding Views – Contest Win Joes 2 Pros Combo (USD 198) – Day 4 of 5 « SQL Server Journey with SQL Authority

  29. Pingback: SQL SERVER – Weekly Series – Memory Lane – #044 | Journey to SQL Authority with Pinal Dave

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s