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