CREATE TABLE dbo.ErrorLog (
  ErrorLogID int IDENTITY 
    CONSTRAINT PK_ErrorLog_ErrorLogID PRIMARY KEY,
  ErrorTime datetime NOT NULL
    CONSTRAINT DF_ErrorLog_ErrorTime DEFAULT (GETDATE()),
  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
)
GO


CREATE PROCEDURE dbo.usp_LogError
  @ErrorLogID int = 0 OUTPUT
AS
BEGIN
  SET NOCOUNT ON;

  -- output parm 0 indicates error info not logged
  SET @ErrorLogID = 0;

  BEGIN TRY
    -- return if no error
    IF ERROR_NUMBER() IS NULL 
      RETURN;

    -- return if inside uncommitable tx
    IF XACT_STATE() = -1
    BEGIN
      PRINT N'Transaction Uncommittable. Cannot Log Error'
      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()
      );

    SELECT @ErrorLogID = @@IDENTITY;
  END TRY
  BEGIN CATCH
    PRINT 'An error occurred in stored procedure usp_LogError'
    EXECUTE dbo.usp_PrintError;
    RETURN -1;
  END CATCH;
END;
GO