SQL Server Error Logging From A Stored Procedure

In this article, you will learn how to log SQL Server exceptions from stored procedures.

1. create a table in which you want to save exceptions

   CREATE TABLE [dbo].[ExceptionLog](
       [id] [int] IDENTITY(1, 1) NOT NULL,
       [ErrorLine] [int] NULL,
       [ErrorMessage] [nvarchar](5000) NULL,
       [ErrorNumber] [int] NULL,
       [ErrorProcedure] [nvarchar](128) NULL,
       [ErrorSeverity] [int] NULL,
       [ErrorState] [int] NULL,
       [DateErrorRaised] [datetime] NULL
   )
   

2. create a stored procedure that inserts the exception details into the ExceptionLog table

   Create Proc [dbo].[spGetErrorInfo]
   as
   begin
   insert into ExceptionLog(
       ErrorLine, ErrorMessage, ErrorNumber,
       ErrorProcedure, ErrorSeverity, ErrorState,
       DateErrorRaised
   )
   SELECT
       ERROR_LINE() as ErrorLine,
       Error_Message() as ErrorMessage,
       Error_Number() as ErrorNumber,
       Error_Procedure() as 'Proc',
       Error_Severity() as ErrorSeverity,
       Error_State() as ErrorState,
       GETDATE() as DateErrorRaised 
   end
   

3. create a student table for testing purposes

   CREATE TABLE [dbo].[student](
       [Id] [int] IDENTITY(1, 1) NOT NULL,
       [Name] [nchar](10) NULL,
       [City] [nchar](10) NULL
   )

4. create a procedure for inserting the data into the student table

   create proc spStudentInsert
   @name varchar(10),
   @city varchar(10)
   AS
   BEGIN
   SET NOCOUNT ON;
   BEGIN TRY
   insert into student(Name, City) values(@name, @city)
   END TRY
   BEGIN CATCH
   EXEC dbo.spGetErrorInfo
   END CATCH
   END

5. Now execute the stored procedure

   exec spStudentInsert 'Raj', 'Varanasi'

   Now see the inserted record

   select * from student

6. Now create an exception in the insert procedure

   ALTER PROCEDURE [dbo].[spStudentInsert]
   @name varchar(10),
   @city varchar(10)
   AS
   BEGIN
   SET NOCOUNT ON;
   BEGIN TRY
   Select 1 / 0; // here i am giving hard coded value only for testing purpose if any run time exception occure it will caught into catch block
   insert into student(Name, City) values(@name, @city)
   END TRY
   BEGIN CATCH
   EXEC dbo.spGetErrorInfo
   END CATCH
   END

7. Now execute stored procedure exec spStudentInsert 'Raj', 'Varanasi'

8. Note. This exception was caught only on the database level. It will not catch in code behind and if you want this exception caught in code behind, then Raise() function like the following.

   RAISERROR (@ErrorMsg, @ErrorSeverity, @ErrorState);