Error Trapping (TRY CATCH) in SQL Server Stored Procedures

Here is a way to add some simple but effective error trapping into your stored procedures:

Full code below.

Points to note:

1. First line of the SP we have a BEGIN TRY

2. At the end of the SP we have the END TRY

3. In the CATCH we grab the error info and put it into a string being careful with the nulls.

4. We then add that error info to our ErrorLog (this is the ErrorLog structure on TheDatabase)

The result is:

Which gives us some pretty useful information as to what has gone wrong.

What do you think?  Got any suggestions?

Hope that helps, 
Jon Bosker

DB Gurus, Australia

CREATE PROCEDURE TestSP

AS

BEGIN TRY

SET NOCOUNT ON;

-- All the other commands go here

SELECT 1/0;

END TRY

BEGIN CATCH

DECLARE @ErrorTrack varchar(MAX) =

 'ErrorNumber: '       +  ISNULL(CAST(ERROR_NUMBER() AS varchar), '0') + 

     '. ErrorSeverity: '   + ISNULL(CAST(ERROR_SEVERITY() AS varchar), '0') + 

     '. ErrorState: '      + ISNULL(CAST(ERROR_STATE() AS varchar), '0') + 

     '. ErrorLine:'        + ISNULL(CAST(ERROR_LINE() AS varchar), '0')  

 INSERT INTO [ErrorLog](Module, ErrorMessage, ErrorTrack, ErrorTime, [Path]) 

VALUES (ISNULL(ERROR_PROCEDURE(),''), ERROR_MESSAGE(), @ErrorTrack, GETDATE(), 'Stored Procedure on ' + DB_NAME())

END CATCH

GO

---------------------------------------------------------

-- Test the results:

EXEC TestSP

SELECT * FROM [ErrorLog] ORDER BY ErrorLogID DESC

-- Cleanup:

DROP PROCEDURE TestSP

Here is the structure of our ErrorLog but you can adapt the code above to use any error log:

CREATE TABLE [dbo].[ErrorLog](

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

[Module] [nvarchar](50) NOT NULL,

[ErrorMessage] [nvarchar](max) NOT NULL,

[ErrorTrack] [nvarchar](max) NOT NULL,

[ErrorTime] [datetime] NOT NULL,

[Path] [nvarchar](1000) 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] TEXTIMAGE_ON [PRIMARY]

GO