SQL Server provides a great option to handle the errors in Transact-SQL code. In case anything goes wrong, we have an option to handle those errors and get notified or details about the error in SQL. It is very easy to handle the errors or exceptions in the SQL server. In this blog, we will see how to handle the errors in SQL Server stored procedures using
Handling errors using TRY…CATCH Statement
We can use
Try...Catch statement to handle the errors in SQL Server stored procedures. Here is the syntax for
BEGIN TRY -- SQL code here END TRY BEGIN CATCH -- This code will be executed when an error occurs in the TRY block. END CATCH
If you want to monitor the errors or handle the errors for your SQL statement then you have to place your code inside the BEGIN TRY and END TRY. If anything goes wrong or an error occurs in your statement then the execution will be shifted into BEGIN CATCH block and start to execute the statements in the CATCH block. We can write the code here to catch the error or log the detailed error for easy resolution.
In the below example,
SELECT 1/0 generates the divide by zero error inside the TRY block. If an error occurs in TRY block then immediately the execution stops in TRY and transferred to the CATCH block and starts executing the statement line by line in the CATCH block.
CREATE PROCEDURE dbo.TryCatchTest AS BEGIN TRY SELECT 1/0 -- Generate a divide-by-zero error -- You can use your SQL Statements here to track the errors END TRY BEGIN CATCH -- Get the error details or Instead of the below statement you can log the error in a table. SELECT ERROR_NUMBER() AS ErrorNumber ,ERROR_SEVERITY() AS ErrorSeverity ,ERROR_STATE() AS ErrorState ,ERROR_PROCEDURE() AS ErrorProcedure ,ERROR_LINE() AS ErrorLine ,ERROR_MESSAGE() AS ErrorMessage; END CATCH
The CATCH block catches the error and returns as a table as shown below,