Error Handling or Exception Handling in SQL Server

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.

1. @@ERROR
2. TRY-CATCH Block

Using @@ERROR
@@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.

General syntax:
The general syntax of @@ERROR error handling mechanism is:

Select @@ERROR

Example:
Execute the following simple SQL statement:

SELECT 1/0

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:
8134

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:
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

Example:
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;

Output:
If we run the above code we will get the previous error in good formant.

System Function
SQL Server provides the following System function that we can use inside our Catch-block for retrieving the details of the error.

• ERROR_MESSAGE()
  It returns the complete description of the error message.
• ERROR_NUMBER()
  It returns the number of the error.
• ERROR_SEVERITY()
  It returns the number of the Severity.
• ERROR_STATE()
  It returns the error state number.
• ERROR_PROCEDURE()
  It returns the name of the stored procedure where the error occurred.
• ERROR_LINE()
  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;

Output:
If you want to see your errors just run the following sample select statement.

Select * From @Temp