articles

Home / DeveloperSection / Articles / Exception handling in SQL Server

Exception handling in SQL Server

Sachindra Singh 5945 12-Feb-2011

In SQL Server TRY..CATCH statement which helps us to handle the errors effectively in the back end. This handling of the exception can provide additional information about the errors. An exception generally rises run time in the process of execution. Error may occur in several possible ways like hardware failure, network failure, out of memory, bugs in programs and other several reasons. As a user we have no idea which error has been occur at what moment. But we should handle such types of errors and give some meaningful message to the user.

Below is a list of the function that can be invoked when an error occurs.

         ERROR_NUMBER()
         ERROR_MESSAGE()
         ERROR_STATE()
         ERROR_SEVERITY()
         ERROR_PROCEDURE()
         ERROR_LINE()

ERROR_NUMBER (  )

Returns the error number of the error that caused the CATCH block of a TRY…CATCH construct to be run.

ERORR_MESSAGE ( )

Returns the complete text of the error message.

ERORR_STATE ( )

When called in a CATCH block, returns the state number of the error message that caused the CATCH block to be run.

ERORR_SEVERITY ( )

Returns the severity of the error that caused the CATCH block of a TRY…CATCH construct to be run.

ERORR_PROCEDURE ( )

Returns the name of the stored procedure or trigger where an error occurred

ERORR_LINE (  )

Returns the line number at which an error occurred that caused the CATCH block of a TRY…CATCH construct to be run.

In this article I am trying to show how to handle exception in SQL Server in this example I created a procedure given name pro and in procedure I wrote a query to retrieve data from table but Student table does not exists in database in this situation query will generate error but SQL Server give us to handle error as shown below:

Created a procedure in SQL Server as shown below:
CREATE PROCEDURE pro  
AS
BEGIN
   SELECT * FROM Student
END
GO   

How to handle error in SQL Server as shown below:  

BEGIN TRY
     EXECUTE pro
END TRY
BEGIN CATCH
     SELECT
          ERROR_NUMBER() as 'ErrorNumber',
          ERROR_MESSAGE() as 'ErrorMessage',
          ERROR_STATE() as 'State',
          ERROR_SEVERITY()as 'Severity',
          ERROR_PROCEDURE() as 'Procedure',
          ERROR_LINE()as'Line';
ENDCATCH;

After execution above query if query generate error message will be show as shown below:

Exception handling in SQL Server

 

 


Updated 04-Mar-2020

Leave Comment

Comments

Liked By