SQL Server error handling

Here in this article I am going to explain how we can handle the error in SQL Server.

SQL server has the built in support to handle the Transact-SQL statements errors. TRY...CATCH constructs allow us to handle the error(s) in SQL server. To use the TRY...CATCH: write the T-SQL statements in TRY section. If any error occurs in TRY section written code, CATCH section T-SQL will be executed and it returns the error description.

Note : We can’t use TRY...CATCH in User defined function. CATCH section will not have executed if there is any syntax error in TRY section.

Syntax:

BEGIN TRY  
  -- Transact-SQL statements that may have error 
END TRY  
BEGIN CATCH  
    -- Write the Transact-SQL statements if an error occurs in code written in TRY section
END CATCH  

We can get the detailed information of errors. To get error info, use the below given functions inside CATCH block:

ERROR_NUMBER() returns the number of the error.

ERROR_STATE() returns the error state number.

ERROR_SEVERITY() returns the severity.

ERROR_PROCEDURE() returns the name of the stored procedure or trigger where the error occurred.

ERROR_LINE() returns the line number inside the routine that caused the error.

ERROR_MESSAGE() returns the complete text of the error message. The text includes the values supplied for any substitutable parameters, such as lengths, object names, or times.

 

Example

Now let’s move to practical. Here in this example I want to know the result of 6/0. On executing this error occurs because as we know divide by 0 is an error.

BEGIN TRY
declare @ans int = 6/0
select @ans as answer
END TRY
BEGIN CATCH
  SELECT
    ERROR_NUMBER() AS ErrorNumber,
    ERROR_STATE() AS ErrorState,
    ERROR_SEVERITY() AS ErrorSeverity,
    ERROR_PROCEDURE() AS ErrorProcedure,
    ERROR_LINE() AS ErrorLine,
    ERROR_MESSAGE() AS ErrorMessage;
END CATCH;

On dividing the 6/2 getting result 3.

BEGIN TRY
declare @ans int = 6/2
select @ans as answer
END TRY
BEGIN CATCH
  SELECT
    ERROR_NUMBER() AS ErrorNumber,
    ERROR_STATE() AS ErrorState,
    ERROR_SEVERITY() AS ErrorSeverity,
    ERROR_PROCEDURE() AS ErrorProcedure,
    ERROR_LINE() AS ErrorLine,
    ERROR_MESSAGE() AS ErrorMessage;
END CATCH;