BEST WAY TO LOG ERRORS IN SQL SERVER

/****** Object: Table [dbo].[Tbl_Log_Error] Script Date: 02/21/2013 18:26:37 ******/

IF EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo].[Tbl_Log_Error]') AND type in (N'U'))

DROP TABLE [dbo].[Tbl_Log_Error]

GO

/****** Object: Table [dbo].[Tbl_Log_Error] Script Date: 02/21/2013 18:26:37 ******/

SET ANSI_NULLS ON

GO

SET QUOTED_IDENTIFIER ON

GO

CREATE TABLE [dbo].[Tbl_Log_Error](

[Id] [int] IDENTITY(1,1) NOT NULL,

[ErrorNumber] [int] NULL,

[ErrorSeverity] [int] NULL,

[ErrorState] [int] NULL,

[ErrorProcedure] [nvarchar](126) NULL,

[ErrorLine] [int] NULL,

[ErrorMessage] [nvarchar](2048) NULL,

[CreatedDate] [datetime] NULL,

[IsRectified] [bit] NULL

) ON [PRIMARY]

GO

/****** Object: StoredProcedure [dbo].[USP_Log_Error] Script Date: 02/21/2013 18:27:32 ******/

IF EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo].[USP_Log_Error]') AND type in (N'P', N'PC'))

DROP PROCEDURE [dbo].[USP_Log_Error]

GO

/****** Object: StoredProcedure [dbo].[USP_Log_Error] Script Date: 02/21/2013 18:27:32 ******/

SET ANSI_NULLS ON

GO

SET QUOTED_IDENTIFIER ON

GO

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

-- Author: Madhan

-- Create date: 21 FEB

-- Description: To Log Error

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

CREATE PROCEDURE [dbo].[USP_Log_Error]

AS

BEGIN

SET NOCOUNT ON;

if(ERROR_NUMBER() is not null)

begin

INSERT INTO Tbl_Log_error

(ErrorNumber,ErrorSeverity,ErrorState,ErrorProcedure,ErrorLine,ErrorMessage,CreatedDate,IsRectified)

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

,getdate() as CreatedDate

,0 as IsRectified

end

END

GO

Create proc TestProcedure

as

Begin

BEGIN TRY

SELECT 1/0

END TRY

BEGIN CATCH

Exec USP_Log_Error

END CATCH

End

--(or)

--if(@@ERROR != 0)

--Exec USP_Log_Error

EXEC TestProcedure