Exception handling in sql server

In this article I am going to explain how to handle exception in sql server.

In previous article I have explained how to replace null value with 0 in sqlserver, how to get month name or number from date using Sql server and how toget local IP address of system/machine using asp.net

Description:

Exception handling was first time introduced in Sql server 2005. To handle exception we use the try catch block in store procedure. We write T-Sql statement in try block and to handle exception write in catch block. In any error occurred in our T-sql statement it will automatically go to catch block.

Syntax:

begin try

---Write code here

end try

BEGIN CATCH

---Write code here

end catch

ERROR_NUMBER(): This function will returns the  number of the error.

ERROR_LINE(): This function retuned the line number inside the routine that caused the error.

ERROR_MESSAGE():This function will returns the error message text, which includes the values supplied for any substitutable parameters, such as times or object names.

ERROR_SEVERITY(): This will return the error’s severity.

ERROR_STATE(): This function will returns the error’s state number.

ERROR_PROCEDURE(): This function will return the name of the stored procedure or trigger that generated the error.

Example:

BEGIN TRY  

    SELECT 6/0; 

END TRY 

BEGIN CATCH 

    SELECT 

       [Error Number]= ERROR_NUMBER()

        ,[ErrorProcedure]=ERROR_PROCEDURE()  

        ,[Error Severity] = ERROR_SEVERITY()

        ,[Error State]=ERROR_STATE()        

        ,[Error Line] = ERROR_LINE()

        ,[Error Message] = ERROR_MESSAGE(); 

END CATCH; 

GO 

Output:

Error Number	Error Procedure	Error Severity	Error State	Error Line	Error Message
8134	NULL	16	1	2	Divide by zero error encountered.