Beta
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
Unknown integration
DataFrameavailable as
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
This query is taking long to finish...Consider adding a LIMIT clause or switching to Query mode to preview the result.
Unknown integration
DataFrameavailable as
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
This query is taking long to finish...Consider adding a LIMIT clause or switching to Query mode to preview the result.
Unknown integration
DataFrameavailable as
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;
This query is taking long to finish...Consider adding a LIMIT clause or switching to Query mode to preview the result.
In previous cells we get the same message When we try to insert new values and insert existing values
Nesting TRY CATCH.
Unknown integration
DataFrameavailable as
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;
This query is taking long to finish...Consider adding a LIMIT clause or switching to Query mode to preview the result.
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:
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;
Unknown integration
DataFrameavailable as
df4
variable
-- If you want to known all numbers messages you must use this code in your computer
-- SELECT * FROM sys.messages
This query is taking long to finish...Consider adding a LIMIT clause or switching to Query mode to preview the result.
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.
Unknown integration
DataFrameavailable as
df5
variable
-- Display customers table
SELECT TOP 5 * FROM sales.customers
This query is taking long to finish...Consider adding a LIMIT clause or switching to Query mode to preview the result.
Unknown integration
DataFrameavailable as
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
This query is taking long to finish...Consider adding a LIMIT clause or switching to Query mode to preview the result.
Unknown integration
DataFrameavailable as
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;
This query is taking long to finish...Consider adding a LIMIT clause or switching to Query mode to preview the result.