Implement Error Handling In SQL Server

Jump To Right Section Show

Like other programming language, Microosft SQL Server also has an error handling model to handle exceptions and errors that occurs in T-SQL statements. We have TRY..CATCH blocks to handle exception/error in Sql Server. We put T-SQL statements in TRY block and to handle exception we write code in CATCH block.

If there is an error in code within TRY block then the control will automatically jump to the corresponding CATCH blocks. In Sql Server, against a Try block, we can have only one CATCH block.

Basic Syntax of TRY..CATCH of Microsoft SQL Server


BEGIN TRY
       --T-SQL statements or statement blocks
END TRY
BEGIN CATCH
       --T-SQL statements or statement blocks
END CATCH

BEGIN TRY

--T-SQL statements or statement blocks

END TRY

BEGIN CATCH

--T-SQL statements or statement blocks

END CATCH

Example of using TRY..CATCH


USE AdventureWorks2014
GO
-- Basic example of TRY...CATCH
 
BEGIN TRY
-- Generate a divide-by-zero error  
  SELECT
    1 / 0 AS Error;
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;
GO

USE AdventureWorks2014

-- Basic example of TRY...CATCH

BEGIN TRY

-- Generate a divide-by-zero error

SELECT

1 / 0 AS Error;

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;

Error Functions used within CATCH block

ERROR_NUMBER(): This returns the error number and its value is the same as for @@ERROR function.
ERROR_LINE(): This returns the line number of T-SQL statement that caused an error.
ERROR_SEVERITY(): This returns the severity level of the error.
ERROR_STATE(): This returns the state number of the error.
ERROR_PROCEDURE(): This returns the name of the stored procedure or trigger where the error occurred.
ERROR_MESSAGE(): This returns the full text of error message. The text includes the values supplied for any substitutable parameters, such as lengths, object names, or times.

Note

A TRY..CATCH block combination catches all the errors that have a severity between 11 and 19.
The CATCH block is executed only if there is an error occurs in T-SQL statements within TRY block otherwise the CATCH block is ignored.
Each TRY block is associated with only one CATCH block and vice versa.
TRY and CATCH blocks can’t be separated with the GO statement. We need to put both TRY and CATCH blocks within the same batch.
TRY..CATCH blocks can be used with transactions. We check the number of open transactions by using @@TRANCOUNT function in Sql Server.
XACT_STATE function within the TRY..CATCH block can be used to check whether an open transaction is committed or not. It will return -1 if the transaction is not committed else returns 1.