SQL Server Error Logging From A Stored Procedure

In this article you will learn how to log SQL Server exception from 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:
    1. CREATE TABLE [dbo].[ExceptionLog](  
    2. [id] [int] IDENTITY(1, 1) NOT NULL,  
    3. [ErrorLine] [intNULL,  
    4. [ErrorMessage] [nvarchar](5000) NULL,  
    5. [ErrorNumber] [intNULL,  
    6. [ErrorProcedure] [nvarchar](128) NULL,  
    7. [ErrorSeverity] [intNULL,  
    8. [ErrorState] [intNULL,  
    9. [DateErrorRaised] [datetime] NULL  
    10. )  
  2. create stored procedure which insert the exception details into ExceptionLog table:
    1. Create Proc [dbo].[spGetErrorInfo]
    2. as
    3. begin
    4. insert into ExceptionLog(  
    5. ErrorLine, ErrorMessage, ErrorNumber,  
    6. ErrorProcedure, ErrorSeverity, ErrorState,  
    7. DateErrorRaised  
    8. )  
    9. SELECT  
    10. ERROR_LINE () as ErrorLine,  
    11. Error_Message() as ErrorMessage,  
    12. Error_Number() as ErrorNumber,  
    13. Error_Procedure() as 'Proc',  
    14. Error_Severity() as ErrorSeverity,  
    15. Error_State() as ErrorState,  
    16. GETDATE () as DateErrorRaised 
    17. end  
  3. create a student table for testing purpose:
    1. CREATE TABLE [dbo].[student](  
    2. [Id] [int] IDENTITY(1, 1) NOT NULL,  
    3. [Name] [nchar](10) NULL,  
    4. [City] [nchar](10) NULL  
    5. )  
  4. create a procedure for inserting the data into student table:
    1. create proc spStudentInsert
    2. @name varchar(10),
    3. @city varchar(10)  
    4. AS  
    5. BEGIN  
    6. SET NOCOUNT ON;  
    7. BEGIN TRY  
    8. insert into student(Name, City) values(@name, @city)  
    9. END TRY  
    10. BEGIN CATCH  
    11. EXEC dbo.spGetErrorInfo  
    12. END CATCH  
    13. END  
  5. Now execute the stored procedure:
    1. exec spStudentInsert 'Raj''Varanasi'  
    Now see the inserted record
    1. select * from student  
    Select

  6. Now create exception in insert procedure:
    1. ALTER PROCEDURE[dbo].[spStudentInsert]
    2. @name varchar(10),
    3. @city varchar(10)  
    4. AS  
    5. BEGIN  
    6. SET NOCOUNT ON;  
    7. BEGIN TRY  
    8. 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  
    9. insert into student(Name, City) values(@name, @city)  
    10. END TRY  
    11. BEGIN CATCH  
    12. EXEC dbo.spGetErrorInfo  
    13. END CATCH  
    14. END  
  7. Now execute stored procedure exec spStudentInsert 'Raj','Varanasi':

    erorlog
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).