Error Handling in SQL Server
In this tutorial, learn how to handle and logs errors in SQL Server.
Join the DZone community and get the full member experience.Join For Free
In this article, we will learn how to handle exceptions in SQL Server and also see how to capture or log the exception in case of any DB Level Exception occurs so that the Developer can refer to that Error log, can check the severity of the Exception, and fix it without wasting too much time in finding the exception causing procedure or function or line which is causing the exception.
In order to demonstrate how an exception is thrown in the procedure, I have created a Sample Procedure i.e. usp_SampleProcedure as shown below
In the above procedure, I have written a query which will thow Divide by Zero Exception on the execution of the procedure.
Now in order to handle this exception, we need to use a try-catch block in the procedure. In case of exception, we will handle or log the exception in the catch block. I have created a Table i.e. DBErrorLogs in order to capture the DB Level Error. The schema of the table is shown below.
Schema of Table DBErrorLogs:
Procedure i.e. usp_SampleProcedure after using Try Catch Block:
As you can see, we are capturing the various fields with the help of predefined functions provided by SQL like UserName, ErrorNumber, ErrorState, ErrorSeverity, ErrorLine, ErrorProcedure, ErrorMessage, ErrorOccuredOn, etc. which will help us to find us the real root causing issue of the Exception. Let’s understand each function quickly.
1. SUSER_SNAME(): Returns the Login Name for the current Security Context.
2. ERROR_NUMBER(): Returns the error number of the error which caused the catch block of a try-catch construct to execute. ERROR_NUMBER() returns NULL when called outside of the scope of the Catch Block.
3. ERROR_STATE(): Returns the State Number of the error message that caused the Catch Block to Run and it returns NULL when called outside of the scope of the Catch Block.
4. ERROR_SEVERITY(): returns the Severity of the Error when the error or exception occurs. There are several levels of Error Severity defined by Microsoft which can be used to identify the type of the problem encountered by the SQL Server. For more, you can visit https://docs.microsoft.com/en-us/sql/relational-databases/errors-events/database-engine-error-severities?view=sql-server-ver15 link. ERROR_SEVERITY() returns NULL when called outside of the scope of the Catch Block.
5. ERROR_LINE(): returns the line number of the occurrence of the Error. ERROR_LINE returns NULL when called outside of the scope of the Catch Block.
6. ERROR_PROCEDURE(): returns the name of the procedure or trigger on the occurrence of the Error. ERROR_PROCEDURE() returns NULL in case error did not occur in the stored procedure or trigger or when called outside of the scope of the Catch Block.
7. ERROR_MESSAGE(): returns the message text of the error that caused the catch block of a try-catch block to execute.
The result on handling the exception with a try-catch block:
Messages which shows the records is inserted in DBErrorLogs Table:
Preview of DBErrorLogs Table:
Rollback Transaction in Case of Any Error/Exception:
Now let see how to rollback a transaction when an error or exception is encountered. For the demonstration, I am using the AdventureWorks Database to show the below demo. I am trying to delete a record in a transaction that cannot be deleted because of the conflict with the reference constraint and throws an error.
In the catch block, we are checking @@TRANCOUNT in order to check whether any transaction is encountered before throwing the error. If @@TRANCOUNT returns a value more than 0, in that case, we will roll back the transaction and log the error details in our DBErrorLogs table.
I hope this article helps you in getting a basic understanding to handle exceptions or errors in the SQL Server.
Published at DZone with permission of Anoop Kumar Sharma, DZone MVB. See the original article here.
Opinions expressed by DZone contributors are their own.