Easy way to track Stored Procedure errors in MS SQL Server 2008

You can easily trace all errors of Stored Procedures in MS SQL Server. To do this, first create a table called Error.

CREATE TABLE [dbo].[Error](
    [iAutoID] [int] IDENTITY(1,1) NOT NULL,
    [dErrorDate] [datetime] NOT NULL,
    [vErrorNumber] [nvarchar](max) NULL,
    [vErrorSeverity] [nvarchar](max) NULL,
    [vErrorState] [nvarchar](max) NULL,
    [vErrorProcedure] [nvarchar](max) NULL,
    [vErrorLine] [nvarchar](max) NULL,
    [vErrorMessage] [nvarchar](max) NULL
) ON [SECONDARY]

GO

ALTER TABLE [dbo].[Error] ADD  CONSTRAINT [DF_Error_dErrorDate]  DEFAULT (getdate()) FOR [dErrorDate]
GO

Now create a Stored Procedure by writing the following code:     

CREATE PROCEDURE [dbo].[prcDailyAttendanceGeneration]
WITH
EXECUTE AS CALLER
AS
BEGIN
    SET XACT_ABORT, NOCOUNT ON
    DECLARE @starttrancount INT
    Begin TRY
        SELECT @starttrancount = @@TRANCOUNT
        IF @starttrancount = 0
        BEGIN TRANSACTION

	-- Your own code start
	Execute prcInsertShiftDateWiseInfo
        -- Your own code end

	IF @starttrancount = 0
        COMMIT TRANSACTION
	End Try
    Begin Catch
        -- Test if the transaction is uncommittable.
        IF  XACT_STATE() <> 0 AND @starttrancount = 0
        BEGIN
            ROLLBACK TRANSACTION;
        END;
	-- This is the main tricks to store all the errors in error table.
	insert into Error(vErrorNumber,vErrorSeverity,vErrorState,vErrorProcedure,vErrorLine,vErrorMessage)
        SELECT ERROR_NUMBER(),ERROR_SEVERITY(),ERROR_STATE(),ERROR_PROCEDURE(),ERROR_LINE(),ERROR_MESSAGE()
	End Catch
END    

 Now if the procedure gives any error, the error details will be saved into the Error table. By this way you can easily get all error details from the Error table and can take the necessary steps.