In this article, we will learn about the concept of error handling in SQL servers. How we can handle errors and give a meaningful message or how we can do something when an error occurs in an SQL statement.
Overview
Error handling in the SQL server gives us control over the SQL statement. When things go wrong with the SQL statement that time we can handle it easily by the error-handling concept. Error handling is as simple as logging something that happened. Using error handling developers can give a more meaningful message that can be easily understandable.
Let’s see the syntax of error handling,
BEGIN TRY
--code to be executed
END TRY
BEGIN CATCH
--code to run if an error occurs in try block
END CATCH
In the above syntax, we can see that In the try block we write a code that we want to monitor for error or keep a code that might give an error at execution time. In the catch block writes a code that we want to execute when an error occurs in the code that writes in the try block and we can fix the error.
We can have special data in the catch block by using some inbuilt method which is shown below,
- ERROR_NUMBER – Returns the internal number of the error
- ERROR_STATE – Returns the information about the source
- ERROR_SEVERITY – Returns the information about anything from informational errors to errors the user of DBA can fix, etc.
- ERROR_LINE – Returns the line number at which an error happened on
- ERROR_PROCEDURE – Returns the name of the stored procedure or function
- ERROR_MESSAGE – Returns the most essential information and that is the message text of the error
Example
begin try
select 1/0 as error
end try
begin catch
select ERROR_NUMBER() AS ErrorNumber,
ERROR_STATE() AS ErrorState,
ERROR_SEVERITY() AS ErrorSeverity,
ERROR_LINE() AS ErrorLine,
ERROR_MESSAGE() AS ErrorMessage;
end catch
Output
I hope you guys found something useful and this article will help you to understand the concept of Error handling in SQL server.
Thank you.
