SQL Server Error Logging From A Stored Procedure

In this article you will learn how to log SQL Server exception from stored procedure: 

1. create table in which you want to save exception:
   

   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 stored procedure which insert the exception details into 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 purpose:
   

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 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 exception in 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':
   
   

Note:

This exception caught only on 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).