Pages

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

No comments:

Post a Comment

Please leave a comment for this post