Error Logging in SQL

While developing application we logs many different types of activities in order to track what is happening in the system.

We logs many different types of errors occurring in the application due to some abnormal operations or behavior of the system based on the activities happening in the system. These errors sometimes we logs into the file system or sometimes logs into the database and give the permission to admin user to get the access of these logs and take the necessary action.

Here, we will see a simple example to track and log the error in SQL server.

-- Verify that the stored procedure does not already exist.
IF OBJECT_ID ( 'usp_GetErrorInfo', 'P' ) IS NOT NULL 
    DROP PROCEDURE usp_GetErrorInfo;
GO

-- Create procedure to retrieve error information.
CREATE PROCEDURE usp_GetErrorInfo
AS
SELECT
    ERROR_NUMBER() AS ErrorNumber
    ,ERROR_SEVERITY() AS ErrorSeverity
    ,ERROR_STATE() AS ErrorState
    ,ERROR_PROCEDURE() AS ErrorProcedure
    ,ERROR_LINE() AS ErrorLine
    ,ERROR_MESSAGE() AS ErrorMessage;
GO

BEGIN TRY
    -- Generate divide-by-zero error.
    SELECT 1/0 As TestData;
END TRY
BEGIN CATCH
    -- Execute error retrieval routine.
    EXECUTE usp_GetErrorInfo;
END CATCH; 

Here in above sample SQL, we have created a stored procedure which we can call when there is an exception while executing the SQL statements.

This procedure will return the details of the SQL exception. You can modify the stored procedure as per your need to log the error into the database. And later you can refer the same table for further analysis.

You can customize the stored procedure and pass the different parameters to make it more descriptive like, you can pass the actual stored procedure name which generating exception as parameter, or name of actual function in front end calling this stored procedure or pass the other information like input pass to actual stored procedure which generated the SQL exception and log the information for future reference to provide the resolution for that.

Advertisements

#error-logging-in-sql-server