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





1.00/5 (1 vote)
Easily trace all errors of Stored Procedures in MS SQL Server.
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.