Error Handling 1

Datasets

Products Table Buyers Table Staff Table Orders Table

• Getting an error

  

    CONSTRAINT unique_product_name UNIQUE(product_name);
  
    INSERT INTO products(product_name, stock, price) 
        VALUES('Trek Powerfly 5 - 2018', 10, 3499.99);
  

  Violation of UNIQUE constraints ‘unique_product_name’. Cannot insert duplicate key in object ‘dbo.products’. The duplicate key value is (Trek Powerfly 5 - 2018).

• The TRY…CATCH syntax

  Syntax:

        BEGIN TRY
            {sql_statement | statement_block}
        END TRY
        BEGIN CATCH
            [{sql_statement | statement_block}]
        END CATCH
        [;]
  

  • Enclose your statements within the TRY block
  • Place your error handling code withint the CATCH block
  • Error in the TRY block -> the CATCH block takes the control
  • No Error in the TRY block -> the CATCH bock is skipped

  Example:

        BEGIN TRY
            INSERT INTO products(product_name, stock, price)
                VALUES('Trek Powerfly 5 - 2018', 10, 3499.99);
            SELECT 'Product inserted correctly!' AS message;
        END TRY
        BEGIN CATCH
            SELECT 'An error occured! You are in the CATCH block' AS message;
        END CATCH
  

  The above sql statement runs the code within the TRY block and retuns the corresponding messages depending upon if the code runs successfully or not.

• Nesting TRY…CATCH

        BEGIN TRY
            INSERT INTO products(product_name, stock, price)
                VALUES('Trek Powerfly 5 - 2018', 10, 3499.99);
            SELECT 'Product inserted correctly!' AS message;
        END TRY
        BEGIN CATCH
            SELECT 'An error occured inserting the product! You are in the first CATCH block' AS message;
            BEGIN TRY
                INSERT INTO myErrors
                    VALUES ('ERROR!');
                SELECT 'Error inserted correctly!' AS message;
            END TRY
            BEGIN CATCH
                SELECT 'An error occured inserting the error! You are in the second CATCH block' AS message;
            END CATCH
        END CATCH
  

---

Error anatomy and uncatchable errors

• Error anatomy

        INSERT INTO products(product_name, stock, price)
            VALUES('Trek Powerfly 5 - 2018', 10, 3499.99);
  

  

• Uncatchable erros

  • Severity lower than 11 (11-19 are catchable)
  • Severity of 20 or higher that stop the connection
  • Compilation errors: objects and columns that don’t exist

---

Giving Information About Errors

• Error Functions

  • ERROR_NUMBER() returns the number of the error.
  • ERROR_SEVERITY() returns the error severity (11-19).
  • ERROR_STATE() returns the state of the error.
  • ERROR_LINE() returns the number of the line of the rror.
  • ERROR_PROCEDURE() returns the name of stored procedure/trigger. NULL if there is not stored procedure/trigger.
  • ERROR_MESSAGE() returns the text of the error message.

        BEGIN TRY
            INSERT INTO products(product_nae, stock, price)
                VALUES('Trek Powerfly 5 - 2018', 10, 3499.99);
        END TRY
        BEGIN CATCH
            SELECT
                ERROR_NUMBER() AS Error_number,
                ERROR_SEVERITY() AS Error_severity,
                ERROR_STATE() AS Error_state,
                ERROR_PROCEDURE() AS Error_procedure,
                ERROR_LINE() AS Error_line,
                ERROR_MESSAGE() AS Error_message
        END CATCH