Course notes: Transactions and Error Handling in SQL Server

In SQL Server, transactions are used to group a set of SQL statements into a single unit of work that either succeeds or fails as a whole. This ensures data integrity and consistency in the database.

To handle errors within a transaction, you can use the TRY...CATCH construct. This allows you to catch and handle any errors that occur during the execution of the transaction.

Here's an example of how to use the TRY...CATCH construct:

BEGIN TRY
    BEGIN TRANSACTION

    -- SQL statements here

    COMMIT TRANSACTION
END TRY
BEGIN CATCH
    -- Error handling code here

    ROLLBACK TRANSACTION
END CATCH

In SQL Server, the BEGIN TRY and END TRY keywords are used to define a block of code where you want to handle potential errors. The code within the TRY block is executed, and if any errors occur, they are caught and handled by the associated CATCH block.

The BEGIN CATCH and END CATCH keywords are used to define the block of code that handles the errors caught in the TRY block. If an error occurs within the TRY block, the execution jumps to the CATCH block, and the code within the CATCH

of how to use the TRY...CATCH construct:

BEGIN TRY
    -- Code that may cause an error

END TRY
BEGIN CATCH
    -- Code to handle the error

END CATCH

1 hidden cell

DataFrameas

df

variable

/* We use products table from database production. We try isert new value to products table and see what happens*/
-- Choose all recird from table
SELECT TOP 3 * FROM production.products

DataFrameas

df2

variable

-- We try to inser new value in product_name and list_price columns and we see What's going on? 
BEGIN TRY
    INSERT INTO production.products (product_name, list_price)
    VALUES ('Product A', 10.99)
END TRY
BEGIN CATCH
    SELECT ERROR_MESSAGE() AS ErrorMessage
END CATCH

DataFrameas

df1

variable

BEGIN TRY
    INSERT INTO production.products (product_name, list_price)
    VALUES ('Trek 820 -2016', 379.99);
END TRY
BEGIN CATCH
    -- Handle the exception/error here
    SELECT ERROR_MESSAGE() AS ErrorMessage;
END CATCH;

In previous cells we get the same message When we try to insert new values and insert existing values

Nesting TRY CATCH.

DataFrameas

df3

variable

-- Now we can nest TRY OR CATCH block inside another TRY.
-- In this nested TRY CATCH SQL Server excuted te second begin try
BEGIN TRY
    INSERT INTO production.products(product_name, list_price) VALUES('tREK Power5-2018',  3499.99);
    SELECT ERROR_MESSAGE() AS message
	BEGIN TRY 
	    SELECT product_name, list_price
	    FROM production.products
    END TRY
    BEGIN CATCH
        SELECT ERROR_MESSAGE() AS message
        SELECT 'An error occurred inserting the product you are in the first catch block' AS message
    END CATCH
END TRY
BEGIN CATCH
    SELECT product_name, list_price
    FROM production.products
    WHERE production.products.list_price >= 3499.99
END CATCH;

NERROR NUMBER

The error number is not provided in the given code cells. However, in SQL Server, error numbers are unique identifiers assigned to each type of error. These error numbers can be used to identify and handle specific errors in the code.

To retrieve the error number in SQL Server, you can use the `ERROR_NUMBER()` function within the `CATCH` block. For example:

BEGIN TRY
    -- Your code here
END TRY
BEGIN CATCH
    SELECT ERROR_NUMBER() AS ErrorNumber;
END CATCH;

DataFrameas

df4

variable

-- If you want to known all numbers messages you must use this code in your computer
-- SELECT * FROM sys.messages

ERROR FUNCTIONS

ERROR_MESSAGE() returns the error message of thelast error that ocurred in the currend session.It return string value that contains the text of the error message.

ERROR_NUMBER() returns the number of error.

ERROR_SEVERITY() returns the severity level of the last error that ocurred in the current session.

The severity level is a inteer value that indicate the severity of the error.

Can divided to three part:

From 1-10 Informational messages e.g command completed successfully.

From 11-16 Warning messages e.g data truncated.

From 17-25 Error messages e.g syntax error means code error.

ERROR_STATE() returns the state number of last error that occurred in the current in the current session.

ERROR_STATE() provides additional information about the error and can be used to dignose and troubleshoot the issue. The state number is a intger value the is unique to each error and can be use in conjunction with the error number and error message togther more information about error.

ERROR_LINE() returns the line number of statement that caused the error. this function returns intger value it indicates the line number of that statement generated the error.

ERROR_PROCEDURE() this function returns the name of the stored procedure or trigger that cause the error. ERROR_PROCEDURE returns the name of the batch or transaction.

DataFrameas

df5

variable

-- Display customers table
SELECT TOP 5 * FROM sales.customers

DataFrameas

df6

variable

BEGIN TRY
    INSERT INTO sales.customers(first_name)
    VALUES('Trek Powerfly5-2018')
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

DataFrameas

df10

variable

BEGIN TRY
    -- Some code that may cause an error
    SELECT 1/0;
END TRY
BEGIN CATCH
    SELECT 
        ERROR_NUMBER() AS error_number,
        ERROR_STATE() AS error_state,
        ERROR_LINE() AS error_line,
        ERROR_SEVERITY() AS error_severity;
END CATCH;

‌
‌
‌