Error handler in SQL

Question

Hi,

How to handle error handler in sql. Please provide example also.

Regards,

Ravikumar.N

 

 

Answer 1

Hi,

There is a pretty good example in the AdventureWorks2008 sample database.

These obejcts are required for the sample:

CREATE TABLE [dbo].[ErrorLog](
	[ErrorLogID] [int] IDENTITY(1,1) NOT NULL,
	[ErrorTime] [datetime] NOT NULL,
	[UserName] [sysname] NOT NULL,
	[ErrorNumber] [int] NOT NULL,
	[ErrorSeverity] [int] NULL,
	[ErrorState] [int] NULL,
	[ErrorProcedure] [nvarchar](126) NULL,
	[ErrorLine] [int] NULL,
	[ErrorMessage] [nvarchar](4000) NOT NULL,
 CONSTRAINT [PK_ErrorLog_ErrorLogID] PRIMARY KEY CLUSTERED 
(
	[ErrorLogID] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
) ON [PRIMARY]

GO



-- uspLogError logs error information in the ErrorLog table about the 
-- error that caused execution to jump to the CATCH block of a 
-- TRY...CATCH construct. This should be executed from within the scope 
-- of a CATCH block otherwise it will return without inserting error 
-- information. 
CREATE PROCEDURE [dbo].[uspLogError] 
  @ErrorLogID [int] = 0 OUTPUT -- contains the ErrorLogID of the row inserted
AS                -- by uspLogError in the ErrorLog table
BEGIN
  SET NOCOUNT ON;

  -- Output parameter value of 0 indicates that error 
  -- information was not logged
  SET @ErrorLogID = 0;

  BEGIN TRY
    -- Return if there is no error information to log
    IF ERROR_NUMBER() IS NULL
      RETURN;

    -- Return if inside an uncommittable transaction.
    -- Data insertion/modification is not allowed when 
    -- a transaction is in an uncommittable state.
    IF XACT_STATE() = -1
    BEGIN
      PRINT 'Cannot log error since the current transaction is in an uncommittable state. ' 
        + 'Rollback the transaction before executing uspLogError in order to successfully log error information.';
      RETURN;
    END

    INSERT [dbo].[ErrorLog] 
      (
      [UserName], 
      [ErrorNumber], 
      [ErrorSeverity], 
      [ErrorState], 
      [ErrorProcedure], 
      [ErrorLine], 
      [ErrorMessage]
      ) 
    VALUES 
      (
      CONVERT(sysname, CURRENT_USER), 
      ERROR_NUMBER(),
      ERROR_SEVERITY(),
      ERROR_STATE(),
      ERROR_PROCEDURE(),
      ERROR_LINE(),
      ERROR_MESSAGE()
      );

    -- Pass back the ErrorLogID of the row inserted
    SET @ErrorLogID = SCOPE_IDENTITY();
  END TRY
  BEGIN CATCH
    PRINT 'An error occurred in stored procedure uspLogError: ';
    RETURN -1;
  END CATCH
END;

Here is a sample sproc using error hanlder in a TRY/CATCH block:

CREATE PROCEDURE [HumanResources].[uspUpdateEmployeeLogin]
  @BusinessEntityID [int], 
  @OrganizationNode [hierarchyid],
  @LoginID [nvarchar](256),
  @JobTitle [nvarchar](50),
  @HireDate [datetime],
  @CurrentFlag [dbo].[Flag]
WITH EXECUTE AS CALLER
AS
BEGIN
  SET NOCOUNT ON;

  BEGIN TRY
    UPDATE [HumanResources].[Employee] 
    SET [OrganizationNode] = @OrganizationNode 
      ,[LoginID] = @LoginID 
      ,[JobTitle] = @JobTitle 
      ,[HireDate] = @HireDate 
      ,[CurrentFlag] = @CurrentFlag 
    WHERE [BusinessEntityID] = @BusinessEntityID;
  END TRY
  BEGIN CATCH
    EXECUTE [dbo].[uspLogError];
  END CATCH;
END;

I hope it helps.

J.

---

There are 10 type of people. Those who understand binary and those who do not.

Answer 2

Hello Ravi,

Please check below links

 

http://weblogs.asp.net/alex_papadimoulis/archive/2005/02/02/365685.aspx

http://www.sqlservercentral.com/articles/news/exceptionhandlinginsqlserver2005/2237/

http://www.sommarskog.se/error-handling-I.html

http://www.sommarskog.se/error-handling-II.html

 

Thanks,

---

Raheel Khan