Create a table to capture errors:-
CREATE TABLE [dbo].[ERRORLOG](
[ErrorLogID] [bigint] IDENTITY(1,1) NOT NULL,
[UserName] [varchar](MAX) NULL,
[ErrorNumber] [int] NULL,
[ErrorSeverity] [int] NULL,
[ErrorState] [int] NULL,
[ErrorProcedure] [varchar](MAX) NULL,
[ErrorLine] [int] NULL,
[ErrorMessage] [varchar](MAX) NULL,
[HostName] [varchar](MAX) NULL,
[Time_Stamp] [timestamp] NULL,
CONSTRAINT [PK_ErrorLog] PRIMARY KEY CLUSTERED (
[ErrorLogID] ASC )WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY] ) ON [PRIMARY]
GO
Create a procedure to write errors to the table:-
/*-- ================================================================
-- Author: Carolyn Richardson
-- Create date: 9th March 2009
-- Description: Adds records into the ErrorLog
-- Use:-
SELECT usp_ErrorHandling
--================================================================*/
CREATE PROCEDURE [dbo].[usp_ErrorHandling] AS -- Declaration statements
DECLARE @UserName VARCHAR(200),
@ErrorNumber INT,
@ErrorSeverity INT,
@ErrorState INT,
@ErrorProcedure VARCHAR(200),
@ErrorLine INT,
@ErrorMessage VARCHAR(4000),
@HostName VARCHAR(200),
@TimeStamp DATETIME,
@ErrMsg VARCHAR(MAX)
SET NOCOUNT ON
-- Initialize variables
SELECT @ErrorNumber = ISNULL(error_number(), 0),
@ErrorMessage = ISNULL(error_message(), 'NULL Message'),
@ErrorSeverity = ISNULL(error_severity(), 0),
@ErrorState = ISNULL(error_state(), 1),
@ErrorLine = ISNULL(error_line(), 0),
@ErrorProcedure = ISNULL(error_procedure(), ''),
@UserName = SUSER_SNAME(),
@HostName = HOST_NAME(),
@ErrMsg = 'There has been as error in Procedure '+ @ErrorProcedure
+ ' Please contact your systems administrator ' +ERROR_MESSAGE(),
@TimeStamp = GETDATE() ;
-- Insert into the dbo.ErrorLog table
INSERT INTO [dbo].[ErrorLog]
(
[UserName],
[ErrorNumber],
[ErrorSeverity],
[ErrorState],
[ErrorProcedure],
[ErrorLine],
[ErrorMessage],
[HostName]
)
VALUES (
@UserName,
@ErrorNumber,
@ErrorSeverity,
@ErrorState,
@ErrorProcedure,
@ErrorLine,
@ErrorMessage,
@HostName
)
-- Raise an error with the details of the exception
RAISERROR(@ErrMsg, @ErrorSeverity, 1)
GO
Template for a Procedure:-
/*-- =============================================================================
Author: Carolyn Richardson
Create date: 5th March 2009
Description: Deletes records in table Addresses
USE: EXEC ProcName 1
Revision history
Updated Date:
By:
Details:
-- =============================================================================*/
ALTER PROCEDURE dbo.ProcName @Variable BIGINT
AS
BEGIN
SET NOCOUNT ON
BEGIN TRY
BEGIN TRANSACTION -- Start the transaction
--=================
--SQL here
--=================
COMMIT -- Commit the transaction
END TRY
BEGIN CATCH -- Catch any errors and Report
IF @@TRANCOUNT > 0 ROLLBACK
EXEC dbo.usp_ErrorHandling
END CATCH
END