Error handling or exception handling is the most vital part of any software development. Generally we are not interested to show any type of application error to end user. Error logs are very important for collecting all types of error data generated by an application. It is more useful during an early or beta release of a product. That’s why it is better to store any kind of exceptions in one place. This can be send by email or others technology to developer. Time to time developer must analyze them and fix all the bugs without knowing clients. It will increase application performance.
There are different ways to save the application errors. It depends on choice. Some common techniques are:
- Save error logs in database
- Save error logs in text file
This article explains how to handle error or exception in SQL Server and how to save it in database. Sometimes we performs some DML operation or use SQL cursor in stored procedure. That time any exceptions may occurred. We need to care about all the errors or exceptions occurred. Summary of the article:
- Error Handling Mechanism
- Using @@ERROR
- Using TRY-CATCH Block
- System Function
- Save Error or Exception in Database
Error Handling Mechanism
MS SQL Server provides two built-in mechanisms for error or exception handling.
- TRY-CATCH Block
@@ERROR is a basic error handling mechanism of SQL Server. It is a global variable. It returns the error number. We have to use it under the statement where error can occur.
The general syntax of @@ERROR error handling mechanism is:
Execute the following simple SQL statement:
It will returns the following error:
Msg 8134, Level 16, State 1, Line 1
Divide by zero error encountered.
Check out the Message and number, it is 8134.
Now, execute the @@Error statement just after this statement and check out the output.
The output is:
When We Should Use @@Error:
There are some scenarios where we should use @@ERROR:
- With insert, delete, update, select into Statement
- While using cursor in SQL Server (open, fetch cursor)
- While executing any stored procedure
Using TRY-CATCH Block
MS SQL Server TRY…CATCH block is a standard approach for exception handling like modern programming languages (C#, PHP, Java, etc). It’s use and syntax is same as normal programming language. Here, we can use nested Try-Catch block in our SQL statement. Try block catch the error and throw it in the Catch block. Catch block then handles the error.
General syntax of TRY…CATCH block is:
-- SQL Statement -- SQL Statement BEGIN TRY -- SQL Statement or Block END TRY BEGIN CATCH -- SQL Statement or Block END CATCH -- SQL Statement
Write the following SQL code and execute it:
BEGIN TRY -- Generate a divide-by-zero error. SELECT 1/0; END TRY BEGIN CATCH 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;
If we run the above code we will get the previous error in good formant.
SQL Server provides the following System function that we can use inside our Catch-block for retrieving the details of the error.
It returns the complete description of the error message.
It returns the number of the error.
It returns the number of the Severity.
It returns the error state number.
It returns the name of the stored procedure where the error occurred.
It returns the line number that caused the error.
Save Error or Exception in Database
We can store your error or exceptions in database for further analysis. At first need to create a table. Following SQL scripts is a representation of the total error handling process.
DECLARE @Temp TABLE ( ErrorNumber int, ErrorSeverity int, ErrorState int, ErrorProcedure int, ErrorLine int, ErrorMessage nvarchar(250) ) --Error handling --Write SQL code in the following style. BEGIN TRY SELECT 1/0; END TRY BEGIN CATCH INSERT INTO @Temp 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;
If you want to see your errors just run the following sample select statement.
Select * From @Temp
4 thoughts on “Error Handling or Exception Handling in SQL Server”
Its good for Error Handling.Thanks cybarlab