Welcome!

Transactions and Error Handling in SQL Server

Miriam Antona

Software Engineer

Topics covered

Chapters 1 and 2: Error handling
Chapter 3: Transactions
Chapter 4: Concurrency in transactions

Dataset: Electric bike store

Products

| product_id | product_name                    | stock | price   |
|------------|---------------------------------|-------|---------|
| 1          | Trek Conduit+ - 2016            | 50    | 2999.99 |
| 2          | Sun Bicycles ElectroLite - 2017 | 47    | 1559.99 |
| 3          | Trek Powerfly 8 FS Plus - 2017  | 41    | 4999.99 |
| 4          | Trek Conduit+ - 2018            | 10    | 2799.99 |
| 5          | Trek CrossRip+ - 2018           | 12    | 4499.99 |

Dataset: Electric bike store

Buyers

| buyer_id | first_name | last_name | email                 | phone     |
|----------|------------|-----------|-----------------------|-----------|
| 1        | Dylan      | Smith     | [email protected]   | 555888999 |
| 2        | John       | Antona    | [email protected]   | 555111222 |
| 3        | Astrid     | Harper    | [email protected] | 555000999 |
| 4        | Angus      | Brown     | [email protected]   | 555222012 |
| 5        | David      | Elcano    | [email protected]  | 555602314 |

Dataset: Electric bike store

Staff

| staff_id | first_name | last_name | email                   | phone     |
|----------|------------|-----------|-------------------------|-----------|
| 1        | Mohammed   | Ferrec    | [email protected] | 555888111 |
| 2        | Dimitri    | Brown     | [email protected]   | 555012012 |
| 3        | Leila      | Merheg    | [email protected]    | 555999133 |
| 4        | Mateo      | Casanovas | [email protected] | 555110996 |
| 5        | Carl       | York      | [email protected]       | 555010011 |

Dataset: Electric bike store

Orders

| order_id | product_id | buyer_id | staff_id | price | order_date |
|----------|------------|----------|----------|-------|------------|
| 1        | 2          | 1        | 5        | 100   | 1559.99    |
| 2        | 2          | 5        | 5        | 100   | 1559.99    |
| 3        | 5          | 10       | 1        | 100   | 4499.99    |
| 4        | 10         | 3        | 3        | 100   | 2799.99    |
| 5        | 15         | 2        | 7        | 100   | 2299.99    |

Getting an error

| product_id | product_name                        | stock | price   |
|------------|-------------------------------------|-------|---------|
| 19         | Trek Powerfly 5 - 2018              | 15    | 3499.99 |

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 KEY constraint 'unique_product_name'. 
Cannot insert duplicate key in object 'dbo.products'. 
The duplicate key value is (Trek Powerfly 5 - 2018).

The TRY...CATCH 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 within the CATCH block
Error in the TRY block -> the CATCH block takes the control
No error in the TRY block -> the CATCH block is skipped

Example with 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 occurred! You are in the CATCH block' AS message;  
END CATCH

| message                                       |
|-----------------------------------------------|
| An error occurred! You are in the CATCH block |

Example with TRY...CATCH

BEGIN TRY
    INSERT INTO products (product_name, stock, price)
        VALUES ('Super new Trek Powerfly', 5, 1499.99);
    SELECT 'Product inserted correctly!' AS message;
END TRY
BEGIN CATCH
    SELECT 'An error occurred! You are in the CATCH block' AS message;
END CATCH

| message                     |
|-----------------------------|
| Product inserted correctly! |

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 occurred 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 occurred inserting the error!
            You are in the second CATCH block' AS message;
    END CATCH    
END CATCH

| message                                   |
|-------------------------------------------|
| An error occurred inserting the product!  |        
| You are in the first CATCH block          |

| message                                   |
|-------------------------------------------|
| An error occurred inserting the error!    |        
| You are in the second CATCH block         |

Let's practice!

Transactions and Error Handling in SQL Server