Error Handling in T-SQL

Error handling in T-SQL:

Error handling in T-SQL is similer to other programming languages as C#,C++,Java.A group of statements  enclosed in TRY  block if an error occurs control pass to CATCH block .

Syntax:

BEGIN TRY

     --T-SQL statemtns

END TRY

BEGIN CATCH

     --Handle exception details

END CATCH

SQL Server provides some functions to deal with Errors

ERROR_NUMBER()	Returns the error number of the error (same value of @@ERROR)
ERROR_SEVERITY()	Returns the severity level of the error.
ERROR_STATE()	Returns the state number of the error.
ERROR_PROCEDURE()	Returns the name of the stored procedure or trigger where an error occurred
ERROR_LINE()	Returns Line Number at which error occurred.
ERROR_MESSAGE()	Returns the error message.

Example:

      BEGIN TRY

        -- Generate a divide-by-zero error.

SELECT 1/0;

END TRY

BEGIN CATCH

    SELECT

        ERROR_NUMBER() AS ErrorNumber,

        ERROR_SEVERITY() AS ErrorSeverity,

        ERROR_STATE() AS ErrorState,

        ERROR_PROCEDURE() AS ErrorProcedure,

        ERROR_LINE() AS ErrorLine,

        ERROR_MESSAGE() AS ErrorMessage;

END CATCH;

Output:

When you handle a large project then it is better approch to create custom table to store all errors occurred during exceution of stored procedures.

CREATE TABLE tblcustomError

(

  errorID INT PRIMARY KEY IDENTITY(1,1),

  errorNumber INT,

  errorState INT,

  errorSeverity INT,

  errorLine INT,

  errorProc VARCHAR(MAX),

  errorMsg VARCHAR(MAX),

  userName VARCHAR(MAX),

  hostName VARCHAR(MAX),

  generateddate DATETIME DEFAULT GETDATE()

              )

    Create procedure to insert errors in tblcustomError.

CREATE PROCEDURE proc_customerrors

AS

BEGIN

 

  DECLARE @errorNumber         INT

  DECLARE @errorState         INT

  DECLARE @errorSeverity      INT

  DECLARE @errorLine          INT 

  DECLARE @errorProc          VARCHAR(1000) 

  DECLARE @errorMsg           VARCHAR(1000) 

  DECLARE @userName           VARCHAR(200) 

  DECLARE @hostName           VARCHAR(200)

             

SELECT  @errorNumber = ERROR_NUMBER() 

       ,@errorState     = ERROR_STATE() 

       ,@errorSeverity  = ERROR_SEVERITY() 

       ,@errorLine      = ERROR_LINE() 

       ,@errorProc      = ERROR_PROCEDURE() 

       ,@errorMsg = ERROR_MESSAGE() 

       ,@userName = SUSER_SNAME() 

       ,@hostName = Host_NAME() 

 

INSERT INTO tblcustomError(errorNumber,errorState,errorSeverity,errorLine,

      errorProc,errorMsg,userName,hostName) 

VALUES(@errorNumber,@errorState,@errorSeverity,@errorLine,

      @errorProc,@errorMsg,@userName,@hostName) 

END

Now in catch block of each stored procedure call proc_customerrors stored procedure.It insert all exception details in tblcustomError.

Ex:

CREATE Procedure proc_testEroor

AS

BEGIN TRY

    -- Generate a divide-by-zero error.

    SELECT 1/0;

END TRY

BEGIN CATCH

EXEC proc_customerrors; --Procedure to handle Errors

END CATCH;

Execute this procedure raise a error that will be inserted in tblcustomeError.

And now run following query

SELECT *FROM dbo.tblcustomError;

TRY CATCH widely used with transaction to Roll back transaction if an exception occurs.
Syntax:

  BEGIN TRY

    BEGIN TRANSACTION

    --================================================

    -- Add Your Code Here

    --================================================

    COMMIT

END TRY

BEGIN CATCH

 ROLLBACK

END CATCH

Prev--->Stored Procedures in T-SQL                                                   Next--->Functions in T-SQL