Stored Procedure Error Tracking in MS SQL Server 2008 Best Practices

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.
Advertisements

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s