Home > DeveloperSection > Articles > Exception handling in SQL Server

Exception handling in SQL Server


Database Database 
Ratings:
0 Comment(s)
 3471  View(s)
Rate this:

Exception handling in SQL Server

  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';

END CATCH;

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

Execption handling in SQL Server

 

 

 


Don't want to miss updates? Please click the below button!

Follow MindStick